# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
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),'')))))))

1 Solution

Accepted Solutions
Creator

Another Option is a Mapping Load. See Attached QVW

Mapping:

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:

[Sales Amount],

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

FROM

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

8 Replies
Partner

can you provide sample data n sample app?

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.

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,

*

;

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

Learning never stops.
Contributor II
Author

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.

Thanks

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:

[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 ;

regards

Learning never stops.
Creator

Another Option is a Mapping Load. See Attached QVW

Mapping:

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:

[Sales Amount],

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

FROM

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

Contributor II
Author

Perfect!!! Thank you so much!

Contributor II
Author

This works pretty good!!

Thanks Aron!