Aggregation in Edit Script different then GUI

[profilejamesbond]

Hi Community,

I have given below data set:

Date	Periods	Franchise	Country	Orders_Numbers
01.01.2022	1	Burger	USA	101
01.01.2022	2	Burger	Canada	101
01.01.2022	3	Burger	Australia	101
01.01.2022	4	Burger	France	102
01.01.2022	5	Burger	Spain	102
01.01.2022	6	Burger	Italy	102

If I write Expression in the GUI: Count(DISTINCT Orders_Numbers) then I'll get 1 for the Orders_Numbers = 101 whenver I'll select / deselect any dimension on the GUI. If I'll clear all the dimensions then I'll get 2. One for each Orders_Numbers due to Count(DISTINCT Order_Numbers).

I want to achieve the same results using Edit Script. How to do it ? I don't want to remove any dimension Filter from the GUI.

I tried this solutions to achieve the same results but the problem is I got 3 for the Orders_Numbers = 101. Becaue the %Key has a unique Periods in the %Key that makes it unique while I count on the GUI then it returns me the 3 not one due to the %Key. If I write DISTINCT in the Expression then I'll always get 1 for all the data set.

Do I need to change the expression after the below load edit script or what is the right way to do it ?

NoConcatenate

Fact:

LOAD 

Date & Periods & Franchise & Country as %Key

Date,

Periods,

Franchise,

Country,

Orders_Numbers

FROM

[Fact_Table.qvd] (qvd);

NoConcatenate

Fact_Aggregation:

LOAD %Key,

     Count(DISTINCT Orders_Numbers) as Orders_Numbers_DISTINCT_Count

Resident Fact

Group By

%Key

;

Expression: Sum(Orders_Numbers_DISTINCT_Count)

[henrikalmen · Accepted solution]

You need to decide what it is you want to count distinct by. Is it distinct order numbers per month or per date or do you just want one value with the number of distinct ordernumbers?

In your example you are counting distinct ordernumbers per each of the four dimensions in %Key.

This is another example:

unique: load distinct Orders_Numbers as uniqueOrderNumbers resident Fact;

This would give you a table with a new field and it will only be unique order numbers. You can count how many rows you have in that table. Or you could do this:

unique: load count(distinct Orders_Numbers) as uniqueOrderNumbersCount resident Fact;

That would give you a table with just one row that holds the value of unique ordernumbers.

But what you should actually do depends on what you want to achieve in the end.

[profilejamesbond · Accepted solution]

Hi @henrikalmen,

Many thanks, your solution with given below enhacements works perfectly with all the dimensions.

Script:

load Orders_Numbers,

count(distinct Orders_Numbers) as uniqueOrderNumbersCount

resident Fact;

Expression: Sum(uniqueOrderNumbersCount) works perfect 

Kind rgards,