Hi,
I have been trying to write a MS Query in MS Excel to retrieve data.
DataV and DQ sheets are from the same work book. The expected output is in a new work book. I have done the connectivity between two work books, importing etc. Need help on writing MSQuery to retrieve the data.
When executing the below query, I am getting "Could not add the table (SELECT"
Sample Input:
DataV sheet:
<tbody>
</tbody>
DQ sheet:
<tbody>
</tbody>
Expected Output:
<tbody>
</tbody>
Thanks in Advance
I have been trying to write a MS Query in MS Excel to retrieve data.
DataV and DQ sheets are from the same work book. The expected output is in a new work book. I have done the connectivity between two work books, importing etc. Need help on writing MSQuery to retrieve the data.
When executing the below query, I am getting "Could not add the table (SELECT"
Code:
SELECT V1.*,V2.* FROM
(SELECT DataV.C1, Sum(DataV.C3)
FROM `C:\Users\user\Desktop\Data.xlsx`.DataV DataV
GROUP BY DataV.C1) AS V1
LEFT OUTER JOIN
(SELECT DataV.C1, Sum(DataV.C3)
FROM `C:\Users\user\Desktop\Data.xlsx`.DataV DataV
WHERE (DataV.C2='Y')
GROUP BY DataV.C1) AS V2
ON V1.C1=V2.C2
Sample Input:
DataV sheet:
Col1 | Col2 | Col3 |
T01 | N | 1 |
T02 | N | 1 |
T03 | N | 1 |
T04 | N | 1 |
T01 | Y | 1 |
T04 | Y | 1 |
T01 | Y | 3 |
<tbody>
</tbody>
DQ sheet:
Col1 | Col2 |
T01 | A |
T02 | B |
T03 | F |
T04 | K |
<tbody>
</tbody>
Expected Output:
Col1 | Sum(Total) | Sum(Col2='Y') | DQ |
T01 | 5 | 4 | A |
T02 | 1 | Null | B |
T03 | 1 | Null | F |
T04 | 2 | 1 | K |
<tbody>
</tbody>
Thanks in Advance