Calculate number of products sold for each city

[andymanu]

Hi All,

I am interested in calculating the number of products sold by the city and the transaction date.

Please refer the attached .qvf file and the respective excel file herewith.

The number of sales units needs to be calculated using the distinct "Customer ID".

Note

I want to calculate the number of sales count during the data load event by creating a new field (Sales Count).

The final outcome should looks like below;

Product	Transaction Data	Sales Count	City
A	10/03/2020	2	Melbourne
B	10/03/2020	2	Melbourne
A	10/03/2020	3	Wellignton
C	10/03/2020	1	Wellignton
B	10/03/2020	1	Wellignton
C	11/03/2020	1	Melbourne
C	11/03/2020	1	Wellignton
B	11/03/2020	2	Melbourne
C	11/03/2020	1	Melbourne
C	11/03/2020	1	Wellignton

After converting the original table into something like above, I want join another data table to the above which got Product, Transaction Data, City and some staff data.

Appreciate your feedback in advance.

Thank you.

Kind regards,

Andy

[andymanu]

Hi All,

Apologies for the question not being clear. I have re-written the question as below. Please ignore the above.

Basically, I need to count the distinct number of "Customer ID"s for a given unique "Product", "City" and "Transaction Date" ( I am planning to create a new Key field by combining the "Product", "City" and "Transaction Date" fields .

My original table is something like below;

Product	Customer ID	Transaction Data	Sales City
A	100	10/03/2020	Melbourne
A	101	10/03/2020	Melbourne
B	102	10/03/2020	Melbourne
B	103	10/03/2020	Melbourne
A	104	10/03/2020	Wellington
A	105	10/03/2020	Wellington
A	106	10/03/2020	Wellington
C	107	10/03/2020	Wellington
B	108	10/03/2020	Wellington
C	109	11/03/2020	Melbourne
C	110	11/03/2020	Wellington
B	111	11/03/2020	Melbourne
B	112	11/03/2020	Melbourne
C	113	11/03/2020	Melbourne
C	114	11/03/2020	Melbourne

And the outcome I need out of the above table is;

Product	Customer ID	Transaction Data	Sales City	Count
A	100	10/03/2020	Melbourne	2
A	101	10/03/2020	Melbourne	2
B	102	10/03/2020	Melbourne	2
B	103	10/03/2020	Melbourne	2
A	104	10/03/2020	Wellington	3
A	105	10/03/2020	Wellington	3
A	106	10/03/2020	Wellington	3
C	107	10/03/2020	Wellington	1
B	108	10/03/2020	Wellington	1
C	109	11/03/2020	Melbourne	3
C	110	11/03/2020	Wellington	1
B	111	11/03/2020	Melbourne	2
B	112	11/03/2020	Melbourne	2
C	113	11/03/2020	Melbourne	3
C	114	11/03/2020	Melbourne	3

For an example, If I consider, "Customer ID"s for Product A, Transaction Date 10/03/2020 and the City Melbourne is 2.

Similarly, "Customer ID"s for Product A, Transaction Date 10/03/2020 and the City Wellington is 3.

Note

In my above output table there are duplicate rows which I'll remove during the data load process.

Appreciate, if someone could help me to calculate the "Count" field during the Data Load process please.

Kind regards,

Andy.

[andymanu · Accepted solution]

Hi All,

I assume that something like below gives me the expected answer for the above query.

Data:
LOAD
Product,
"Customer ID",
"Transaction Date",
"Sales City",
RowNo() as CountNo

FROM [lib://XXX/Customer Data.xlsx]
(ooxml, embedded labels, table is [Sales Data]);

Final_Data:

Load
Product,
"Sales City",
"Transaction Date",
Count(CountNo) AS Value

Resident Data
Group By Product,"Sales City", "Transaction Date";

Drop Table Data;

If you know a better way of accomplishing this task, please advice me.

Thank you.

Kind regards,

Andy