Columns with conditional removal of data

[psk180590]

Hello All,

I have my sample data in the below format.

ID	REG	State	Present
1	AA	100	4
1	AA	100	5
1	AA	100	6
1	AA	100	3
2	AB	110	1
2	AB	110	4
3	CR	130	6
5	VX	120	8
6	GG	160	9

I'm looking to generate 4 new Columns here:

1. Count of All Distinct ID's in the country : 9 (Should now change even when a REG or State is selected)

2. Count of Distinct ID's per State  Ex: State 100 is 4 (Should not change when REG is selected)

3. Sum of Present in the Country :46 (Should now change even when a REG or State is selected)

4. Sum of Present per State Ex:State 100 has 18 (Should not change when REG is selected)

I have attached my sample app also along with the sample data and Expected result.

Note: I want this columns to be generated in the Script not in the Application using Set Analysis.

TIA!!

[Or]

You could, in theory, pre-calculate these values for each combination in advance using GROUP BY. However, they will not be responsive to ANY selections if you take this approach. There's no sane way to pre-calculate values during script load for every possible combination of field selections - that's something that should be handled in chart expressions.

[psk180590]

Thanks Shoham for the suggestion.

My problem with this approach is, at the end i want to create a bar chart with all the 3 values stacked against each other.

Due to Section Access say a Person from Region AA logs in to the system he/she would always see the State and Country values equal to his/her.

[Or]

So you are looking to generate a chart that contains pre-aggregated values that would otherwise be blocked by Section Access? I guess you could create a data island of the pre-aggregated data, but if you want it to respect selections made by the user (and you'd probably only want it to respect specific selections), you'd have to explicitly write those into your set analysis.

Perhaps someone else has a better approach - I'm not familiar with any, but this isn't something I've had to implement on a large scale before.

[OmarBenSalem]

1) count(total{<REG>} IDA)

2)count(total <State>  IDA)

3)sum(total{<REG>} Present)

4) sum(total<State> Present)

result:

[psk180590]

The basic idea here is every person sees only his/her data. But, just to see the performance we are stacking individual figures against the State and the Country.

[psk180590]

I don't want to do it at the application level using Set Analysis. Rather generate them at the Script level and read them as new Column at the application level.

[psk180590]

stalwar1‌       loveisfail

mrkachhiaimpstabben23mrkachhiaimp

Just in case if you had any such experiences before.

[OmarBenSalem · Accepted solution]

table:

LOAD

    ID,

    RowNo() as row,

    Reg,

    State,

    Present

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

load sum(Present) as expression4, State Group by State;

LOAD

State,

    Present,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

load count(ID) as expression2, State Group by State;

LOAD

State,

    ID,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

LOAD

    sum( Present) as expression3,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

LOAD

    count(ID) as expression1,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

finale:

NoConcatenate

load *  Resident table;

drop table table;

let vExp1= Peek('expression1',0, 'finale');

let vExp3= Peek('expression3',0, 'finale');

Last:

NoConcatenate

load *, '$(vExp1)' as NewExp1,'$(vExp3)' as NewExp3  Resident finale;

drop fields expression1 , expression3,row;

drop Table finale;

result:

[OmarBenSalem]

table:

LOAD

    ID,

    RowNo() as row,

    Reg,

    State,

    Present

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

load sum(Present) as expression4, State Group by State;

LOAD

State,

    Present,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

load count(ID) as expression2, State Group by State;

LOAD

State,

    ID,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

LOAD

    sum( Present) as expression3,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

left Join(table)

LOAD

    count(ID) as expression1,

    RowNo() as row

FROM [lib://Mariage/Sample (1).xlsx]

(ooxml, embedded labels, table is [Sample Data]);

finale:

NoConcatenate

load *  Resident table;

drop table table;

let vExp1= Peek('expression1',0, 'finale');

let vExp3= Peek('expression3',0, 'finale');

Last:

NoConcatenate

load *, '$(vExp1)' as NewExp1,'$(vExp3)' as NewExp3  Resident finale;

drop fields expression1 , expression3;

drop Table finale;

result: