topic Re: Load * inline in QlikView

Load * inline

bharatkishore — Tue, 08 Mar 2016 06:16:15 GMT

Hi All,

I have written code in edit script as

Threshold:

LOAD * INLINE

[

Threshold, ThresholdCode

<50K, 1

>50K, 2

>100K, 4

>250K, 8

>500K, 16

];

In the front end i am displaying as

Now i need to display one more field called "All" which calcuates all i.e. <50K to >500K.

Can you please tell me how to achieve this.

Thanks,

Bharat

Re: Load * inline

avinashelite — Tue, 08 Mar 2016 06:20:59 GMT

Try like this :

Temp:

LOAD * INLINE

[

Th, Tc

All-<50K, 1

All->50K, 2

All->100K, 4

All->250K, 8

All->500K, 16

];

Threshold:

LOAD subfield(Th,'-') as Threshold,

ThresholdCode

resident

Temp;

drop table Temp;

Re: Load * inline

tresB — Tue, 08 Mar 2016 06:22:08 GMT

try like:

LOAD * INLINE

[

Threshold, ThresholdCode

<50K, 1

>50K, 2

>100K, 4

>250K, 8

>500K, 16

All, 1

All, 2

All, 4

All, 8

All, 16

];

This is the basic concept, you could do it differently for big tables by loading the same table once again and concatenating it, with 'All' as value for all records.

Re: Load * inline

bharatkishore — Tue, 08 Mar 2016 06:35:33 GMT

HI Tresesco,

Thanks a lot for the reply.

With your code i am able to display "All".

The only thing i need to know is

Will this calculate <50K+>50K+>100K+>250K+>500K

Re: Load * inline

tresB — Tue, 08 Mar 2016 06:38:03 GMT

Yes. Try and let know if it doesn't.

Re: Load * inline

bharatkishore — Tue, 08 Mar 2016 06:38:07 GMT

I have given expression in chart as(in Expression Tab)

=if(1= (SUM(ThresholdCode) bitand 1),

if(SUM([LC2 Amount])<50000, SUM([LC2 Amount]), NULL()),

If(2 = (SUM(ThresholdCode) bitand 2), //>50K

if(SUM([LC2 Amount])>=50000 AND SUM([LC2 Amount])<100000, SUM([LC2 Amount]), NULL()),

If(4 = (SUM(ThresholdCode) bitand 4), //>100K

if(SUM([LC2 Amount])>=100000 AND SUM([LC2 Amount])<250000, SUM([LC2 Amount]), NULL()),

If(8 = (SUM(ThresholdCode) bitand 8), //>250K

if(SUM([LC2 Amount])>=250000 AND SUM([LC2 Amount])<500000, SUM([LC2 Amount]), NULL()),

If(16 = (SUM(ThresholdCode) bitand 16), //>500K

if(SUM([LC2 Amount])>=500000, SUM([LC2 Amount]), NULL()),

NULL())))))

Here also i need to include ALL. Can you please tell me how can i add "All" here.

Re: Load * inline

tresB — Tue, 08 Mar 2016 06:43:02 GMT

I am not sure if what you are doing is really needed. I guess the expression could be optimized. Could you post a sample data explaining your requirement?

Re: Load * inline

bharatkishore — Tue, 08 Mar 2016 07:00:38 GMT

The thing i needed is i need to display values like <50K, >50K,>100K,>250K,>500K in a list box so that if the user selects the chart should be changed as per the selection what we do in the list box.

For that i have written this code in backend

Threshold:

LOAD * INLINE

[

Threshold, ThresholdCode

<50K, 1

>50K, 2

>100K, 4

>250K, 8

>500K, 16

];

Now my requirement is i need to display "All" for which calculates the <50+>50... This selection should get reflected in the chart which you can see in the below app. For that i have written expression as

=if(1= (SUM(ThresholdCode) bitand 1),

if(SUM([LC2 Amount])<50000, SUM([LC2 Amount]), NULL()),

If(2 = (SUM(ThresholdCode) bitand 2), //>50K

if(SUM([LC2 Amount])>=50000 AND SUM([LC2 Amount])<100000, SUM([LC2 Amount]), NULL()),

If(4 = (SUM(ThresholdCode) bitand 4), //>100K

if(SUM([LC2 Amount])>=100000 AND SUM([LC2 Amount])<250000, SUM([LC2 Amount]), NULL()),

If(8 = (SUM(ThresholdCode) bitand 8), //>250K

if(SUM([LC2 Amount])>=250000 AND SUM([LC2 Amount])<500000, SUM([LC2 Amount]), NULL()),

If(16 = (SUM(ThresholdCode) bitand 16), //>500K

if(SUM([LC2 Amount])>=500000, SUM([LC2 Amount]), NULL()),

NULL())))))

Now i need to add "All" calcuation here. Please suggest.

Thanks in advance..

Re: Load * inline

tresB — Tue, 08 Mar 2016 07:07:54 GMT

Not sure if I got you right. I still find it a simple case of Sum(ThresholdCode) expression with selection.

Re: Load * inline

bharatkishore — Tue, 08 Mar 2016 07:12:33 GMT

Thanks a lot....