Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Calculate number of products sold for each city

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;

ProductTransaction DataSales CountCity
A10/03/20202Melbourne
B10/03/20202Melbourne
A10/03/20203Wellignton
C10/03/20201Wellignton
B10/03/20201Wellignton
C11/03/20201Melbourne
C11/03/20201Wellignton
B11/03/20202Melbourne
C11/03/20201Melbourne
C11/03/20201Wellignton

 

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

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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

 

View solution in original post

2 Replies
Highlighted
Creator II
Creator II

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;

ProductCustomer IDTransaction DataSales City
A10010/03/2020Melbourne
A10110/03/2020Melbourne
B10210/03/2020Melbourne
B10310/03/2020Melbourne
A10410/03/2020Wellington
A10510/03/2020Wellington
A10610/03/2020Wellington
C10710/03/2020Wellington
B10810/03/2020Wellington
C10911/03/2020Melbourne
C11011/03/2020Wellington
B11111/03/2020Melbourne
B11211/03/2020Melbourne
C11311/03/2020Melbourne
C11411/03/2020Melbourne

 

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

ProductCustomer IDTransaction DataSales CityCount
A10010/03/2020Melbourne2
A10110/03/2020Melbourne2
B10210/03/2020Melbourne2
B10310/03/2020Melbourne2
A10410/03/2020Wellington3
A10510/03/2020Wellington3
A10610/03/2020Wellington3
C10710/03/2020Wellington1
B10810/03/2020Wellington1
C10911/03/2020Melbourne3
C11011/03/2020Wellington1
B11111/03/2020Melbourne2
B11211/03/2020Melbourne2
C11311/03/2020Melbourne3
C11411/03/2020Melbourne3

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.

 

Highlighted
Creator II
Creator II

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

 

View solution in original post