Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
letlotlo
Contributor II
Contributor II

How to move complex calculated dimension to load script?

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.

1 Solution

Accepted Solutions
aronwilliamson
Creator
Creator

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$);

View solution in original post

8 Replies
shraddha_g
Partner
Partner

can you provide sample data n sample app?

aronwilliamson
Creator
Creator

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 /\ >




pradosh_thakur
Master II
Master II

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

Learning never stops.
letlotlo
Contributor II
Contributor II

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

pradosh_thakur
Master II
Master II

Hi

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

Learning never stops.
aronwilliamson
Creator
Creator

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$);

View solution in original post

letlotlo
Contributor II
Contributor II

Perfect!!! Thank you so much!

letlotlo
Contributor II
Contributor II

This works pretty good!!

Thanks Aron!