topic Re: How to move complex calculated dimension to load script? in QlikView

How to move complex calculated dimension to load script?

Anonymous — Thu, 09 Nov 2017 08:58:25 GMT

Hi experts, please assist with the below...

So I have created a calculated dimension to group my sales values into different bucket, can anybody please help me understand how I can have this calculated dimension loaded in the back end script rather than in the front end?

=if(Aggr(Sum(SALES_AMOUNT), RETAILER_ID)>=0 and Aggr(Sum(SALES_AMOUNT), RETAILER_ID) < 1,Dual('R0-R0',1),

if(Aggr(Sum(SALES_AMOUNT), RETAILER_ID)>=1 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 10000,Dual('R1 - R10,000',2),

if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=10001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 50000,Dual('R10,001 - R50,000',3),

if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=50001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 250000,Dual('R50,001 - R250,000',4),

if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=250001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 500000,Dual('R250,001 - R500,000',5),

if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=500001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 3000000,Dual('R500,001 - R3mil',6),

if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=3000001,Dual('> R3 milllion',7),'')))))))

Thanks for your response.

Re: How to move complex calculated dimension to load script?

shraddha_g — Thu, 09 Nov 2017 09:06:40 GMT

can you provide sample data n sample app?

Re: How to move complex calculated dimension to load script?

Anonymous — Thu, 09 Nov 2017 09:11:35 GMT

Resident Load the table that contains these fields, add the key field, calculated field and then any other fields that you want to keep.

LOAD RETAILER_ID as %Key_Retailer_ID,

if(Aggr(Sum(SALES_AMOUNT), RETAILER_ID)>=0 and Aggr(Sum(SALES_AMOUNT), RETAILER_ID) < 1,Dual('R0-R0',1), if(Aggr(Sum(SALES_AMOUNT), RETAILER_ID)>=1 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 10000,Dual('R1 - R10,000',2), if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=10001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 50000,Dual('R10,001 - R50,000',3), if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=50001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 250000,Dual('R50,001 - R250,000',4), if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=250001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <=500000,Dual('R250,001 - R500,000',5), if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=500001 and Aggr(Sum(SALES_AMOUNT),RETAILER_ID) <= 3000000,Dual('R500,001 - R3mil',6), if(Aggr(Sum(SALES_AMOUNT),RETAILER_ID)>=3000001,Dual('> R3 milllion',7),''))))))) as GROUP,

;

<Here is the original table underneath your Resident LOAD /\ >

Re: How to move complex calculated dimension to load script?

pradosh_thakur — Thu, 09 Nov 2017 09:13:23 GMT

load RETAILER_ID, if(Sum(SALES_AMOUNT)>=0 and Sum(SALES_AMOUNT) < 1,Dual('R0-R0',1),

if(Sum(SALES_AMOUNT)>=1 and Sum(SALES_AMOUNT) <= 10000,Dual('R1 - R10,000',2),

if(Sum(SALES_AMOUNT)>=10001 and Sum(SALES_AMOUNT) <= 50000,Dual('R10,001 - R50,000',3),

if(Sum(SALES_AMOUNT)>=50001 and Sum(SALES_AMOUNT) <= 250000,Dual('R50,001 - R250,000',4),

if(Sum(SALES_AMOUNT)>=250001 and Sum(SALES_AMOUNT) <= 500000,Dual('R250,001 - R500,000',5),

if(Sum(SALES_AMOUNT)>=500001 and um(SALES_AMOUNT) <= 3000000,Dual('R500,001 - R3mil',6),

if(Sum(SALES_AMOUNT)>=3000001,Dual('> R3 milllion',7),''))))))) as Bucket

resident table_name

group by RETAILER_ID ;

table name should be the table which have RETAILER_ID

regards

Pradosh

Re: How to move complex calculated dimension to load script?

Anonymous — Thu, 09 Nov 2017 09:58:06 GMT

Thank you very much guys for your reply, your approaches seem to be kinda working but not that well and I think it could be because of something wrong that I am doing on my end.

Please see attached sample data and app for further assistance and advise on this please.

Thanks

Re: How to move complex calculated dimension to load script?

pradosh_thakur — Thu, 09 Nov 2017 10:13:56 GMT

Please find the attached. I have changed the name a bit to show you the data is matching.

else this should be your exact script

Sales:

LOAD RETAILER_ID,

[Sales Amount]

FROM

(biff, embedded labels, table is Sheet1$);

load RETAILER_ID, if(Sum(SALES_AMOUNT)>=0 and Sum(SALES_AMOUNT) < 1,Dual('R0-R0',1),

if(Sum(SALES_AMOUNT)>=1 and Sum(SALES_AMOUNT) <= 10000,Dual('R1 - R10,000',2),

if(Sum(SALES_AMOUNT)>=10001 and Sum(SALES_AMOUNT) <= 50000,Dual('R10,001 - R50,000',3),

if(Sum(SALES_AMOUNT)>=50001 and Sum(SALES_AMOUNT) <= 250000,Dual('R50,001 - R250,000',4),

if(Sum(SALES_AMOUNT)>=250001 and Sum(SALES_AMOUNT) <= 500000,Dual('R250,001 - R500,000',5),

if(Sum(SALES_AMOUNT)>=500001 and Sum(SALES_AMOUNT) <= 3000000,Dual('R500,001 - R3mil',6),

if(Sum(SALES_AMOUNT)>=3000001,Dual('> R3 milllion',7),''))))))) as Bucket

resident Sales

group by RETAILER_ID ;

Please adjust the path in bold.

regards

Pradosh

Re: How to move complex calculated dimension to load script?

Anonymous — Thu, 09 Nov 2017 10:58:41 GMT

Another Option is a Mapping Load. See Attached QVW

Mapping:

Mapping LOAD RETAILER_ID,

if(Sum([Sales Amount])>=0 and Sum([Sales Amount]) < 1,Dual('R0-R0',1),

if(Sum([Sales Amount])>=1 and Sum([Sales Amount]) <= 10000,Dual('R1 - R10,000',2),

if(Sum([Sales Amount])>=10001 and Sum([Sales Amount]) <= 50000,Dual('R10,001 - R50,000',3),

if(Sum([Sales Amount])>=50001 and Sum([Sales Amount]) <= 250000,Dual('R50,001 - R250,000',4),

if(Sum([Sales Amount])>=250001 and Sum([Sales Amount]) <= 500000,Dual('R250,001 - R500,000',5),

if(Sum([Sales Amount])>=500001 and Sum([Sales Amount]) <= 3000000,Dual('R500,001 - R3mil',6),

if(Sum([Sales Amount])>=3000001,Dual('> R3 milllion',7),'')))))))

FROM

(biff, embedded labels, table is Sheet1$)

Group By RETAILER_ID;

Sales:

LOAD RETAILER_ID,

[Sales Amount],

ApplyMap('Mapping',RETAILER_ID,0) As Bucket

FROM

(biff, embedded labels, table is Sheet1$);

Re: How to move complex calculated dimension to load script?

Anonymous — Thu, 09 Nov 2017 12:10:33 GMT

Perfect!!! Thank you so much!

Re: How to move complex calculated dimension to load script?

Anonymous — Thu, 09 Nov 2017 12:11:07 GMT

This works pretty good!!

Thanks Aron!