topic Re: Columns with conditional removal of data in QlikView

Columns with conditional removal of data

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

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!!

Re: Columns with conditional removal of data

Or — Thu, 23 Nov 2017 14:51:18 GMT

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.

Re: Columns with conditional removal of data

psk180590 — Thu, 23 Nov 2017 14:55:39 GMT

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.

Re: Columns with conditional removal of data

Or — Thu, 23 Nov 2017 15:03:17 GMT

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.

Re: Columns with conditional removal of data

OmarBenSalem — Thu, 23 Nov 2017 15:07:26 GMT

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

2)count(total <State> IDA)

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

4) sum(total<State> Present)

result:

Re: Columns with conditional removal of data

psk180590 — Thu, 23 Nov 2017 15:08:42 GMT

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.

Re: Columns with conditional removal of data

psk180590 — Thu, 23 Nov 2017 15:09:52 GMT

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.

Re: Columns with conditional removal of data

psk180590 — Thu, 23 Nov 2017 15:24:48 GMT

stalwar1‌ loveisfail

mrkachhiaimp stabben23 mrkachhiaimp

Just in case if you had any such experiences before.

Re: Columns with conditional removal of data

OmarBenSalem — Thu, 23 Nov 2017 15:58:19 GMT

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:

Re: Columns with conditional removal of data

OmarBenSalem — Thu, 23 Nov 2017 16:25:56 GMT

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:

Re: Columns with conditional removal of data

Anil_Babu_Samineni — Thu, 23 Nov 2017 16:38:56 GMT

We have some limits in scripting. But, In fact we doesn't have concept ignore field concept in Script level. Hence, It can not be done. Can we know the reason why it is not possible in UI?

Re: Columns with conditional removal of data

OmarBenSalem — Fri, 24 Nov 2017 10:21:50 GMT

psk180590‌ ; did this do the trick?

Re: Columns with conditional removal of data

psk180590 — Fri, 24 Nov 2017 10:33:49 GMT

Thanks much, the solution is almost perfect.

The Sum(Present) works perfectly. Only the Count doesn't seem to be satisfied yet

Re: Columns with conditional removal of data

psk180590 — Fri, 24 Nov 2017 10:34:45 GMT

If possible can you please share the sample app you created

Re: Columns with conditional removal of data

psk180590 — Fri, 24 Nov 2017 12:12:53 GMT

Made slight modifications to the script and it works perfectly. Thanks!!

Re: Columns with conditional removal of data

OmarBenSalem — Fri, 24 Nov 2017 12:47:28 GMT

good to know !

Re: Columns with conditional removal of data

psk180590 — Wed, 29 Nov 2017 09:57:50 GMT

Hello omarbensalem

I'm experiencing some problems with particular selections in my app. If possible can you share the Test/Sample you created?

Thanks

Sai.

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

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

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