Using Distinct Count in Qlik Sense load script

[sudeep78]

Dear Experts,

I am a fairly new Qlik user and in the process of self-learning QS script, thanks to the extensive support available in these forums. I would like to have help in resolving an issue related to using DISTINCT COUNT in the LOAD script. Grateful for any help that can be provided.

For the sample data set, I want to get the count of distinct store code while loading the data. The expected result is "9", however, I am getting 12. 

I have tried two different syntax as given below:

Load [Trx DATE],ITEM,STORE_CODE,
Count(DISTINCT (STORE_CODE)) as [Total_Distinct_Store_Count]
resident DataTable
Group By [Trx DATE],[ITEM],STORE_CODE;

Load Distinct
[Trx DATE], ITEM,STORE_CODE,
count(DISTINCT STORE_CODE) as [NewDistinctCount]
Resident DataTable
group By [Trx DATE],ITEM,STORE_CODE;

Both are returning the value "12".

I can get the value in the application using the count distinct formula; however, I need this to be calculated at load time so that further calculations and some data filtering can be performed.

I tried the solution in this thread. But could not get it to work.

Thank you in advance for the support.

[martinpohl · Accepted solution]

because of you are using store_code in your group statement, this happens

use instead:

Load [Trx DATE],ITEM,
Count(DISTINCT (STORE_CODE)) as [Total_Distinct_Store_Count]
resident DataTable
Group By [Trx DATE],[ITEM];

or count in chart

count(distinct total <[Trx Date],ITEM] STORE_CODE)

Regards