Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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:
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.
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.
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.
1) count(total{<REG>} IDA)
2)count(total <State> IDA)
3)sum(total{<REG>} Present)
4) sum(total<State> Present)
result:
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.
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.
mrkachhiaimpstabben23mrkachhiaimp
Just in case if you had any such experiences before.
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:
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: