Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic scripting

hi Qv,

Temp:

LOAD Name,

     Date,

     Year(Date) as Year,

     Sales

FROM

Data.xlsx

(ooxml, embedded labels, table is [Dummy Sales]);

Temp1:

NoConcatenate

LOAD

*

Resident Temp

Order by Name, Date;

DROP Table Temp;

CumulativeSum:

NoConcatenate

LOAD

     Name,

     Date,

     Year,

     Sales,  

     If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum    

Resident Temp1;

DROP Table Temp1;

---------------------------------------------------------------------------------

i have done Cumulative sum for above and the problem is i need commission for Different names commision may vary how to give dynamic in set analysic plz help me

Name

Year

Slab

Commission

Peter

2014

$0-$50000

1%

peter

2014

$50001-$100000

1.5%

peter

2014

$100001-$150000

2%

peter

2014

>$150001

2.5%

john 2014$0-$50000

1.5%

like this the commision varies plz help

-lohi

20 Replies
Not applicable
Author

hi jagan ,

given attachment we got cumulative sum but the reqmnt is we have to add a commission field that may differs to all user (Commission may vary depends on users  ) so how to give dynamic commission depending on users

for example

john may have >=$5000 commission =1%

peter may have >=$5000 commission =2%

-lohi

Not applicable
Author

hi QV's ,

Any idea on this dynamic content,

-lohi

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this script

Temp:

LOAD Name,

     Date,

     year(Date) as Year,

     Sales

FROM

[Data (3).xlsx]

(ooxml, embedded labels, table is [Dummy Sales]);

CumulativeSum:

LOAD

*,

If(CumulativeSum > 150001, '>$150001',

     If(CumulativeSum >= 100001, '$100001-$150000',

     If(CumulativeSum >= 50001,  '$50001-$100000',

      If(CumulativeSum >= 0, '$0-$50000')))) AS Slab;

LOAD

     Name,

     Date,

     Year,

     Sales,  

     If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum;

LOAD

*

Resident Temp

Order by Name, Date;

DROP Table Temp;

Left Join(CumulativeSum)

LOAD

Name, Year, Slab,

Num(Num#(Commission, '#.##%'), '#.00') AS Commission

INLINE [  

    Name, Year, Slab, Commission

    Peter, 2014, $0-$50000, 1%

    Peter, 2014, $50001-$100000, 1.50%

    Peter, 2014, $100001-$150000, 2%

    Peter, 2014, >$150001, 2.50%

    Tom, 2014, $0-$50000, 1.50%

];

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

hi jagan,

thxs for reply i have worked on this but the reqmnt is if we have millon names then we cannot create that many in inline right, the point is we have to use interval match fuction between commission and cumulative sum its should loop and check . i dont knw for loop thts why iam strucked

-lohi

jagan
Luminary Alumni
Luminary Alumni

Hi,

I did this in Inline because your excel file has no data, you can change the script to load from your DataSource, I think this is enough no need of IntervalMatch().

Regards,

Jagan.

Not applicable
Author

Temp:

LOAD Name,

     Date,

     year(Date) as Year,

     Sales

FROM

(ooxml, embedded labels, table is [Dummy Sales]);

Temp1:

NoConcatenate

LOAD

*

Resident Temp

Order by Name, Date;

DROP Table Temp;

CumulativeSum:

NoConcatenate

LOAD

     Name,

     Date,

     Year,

     Sales,  

     If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum    

Resident Temp1 Order by Name,Date;

DROP Table Temp1;

dd1:

LOAD * INLINE [

    StartR,EndR,Commision

    0,50000,1%

    50001,100000,2%

    100001,150000,3%

    150001,200000,4%

];

IntervalMatch (CumulativeSum) LOAD StartR as Start,EndR Resident dd1 ;

here my problem is dynamicly we can change the Cummision if i want to give peter 10% i should do tht , how can i do tht ?

-lohi

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach your sample Commision data in Excel file?  Your commision data should consists of StartR,EndR, Name, Commision, then only we can do this.

Regards,

Jagan.

Not applicable
Author

hi  jagan ,

Please find attachment

- lohi

Not applicable
Author

hi jagan ,

in the attachment Slab needs to check Cumulative sum and then it should check Name and Year and final its should get Commission .

-lohi

Not applicable
Author

hi jagan ,

If data is like this how to do ,

Name            Year             Start             End     Commission
Peter20140600001.0%
Peter2014600011000001.5%
Peter20141000011500002.0%
Peter20150500001.0%
Peter2015500011000001.5%
Peter20151000011500002.0%
Tom20140500001.2%
Tom2014500011000001.6%
Tom20141000011500002.5%
Tom20150500001.2%
Tom2015500011000001.6%
Tom20151000011500002.5%
Mark20140500000.8%
Mark201450001800001.4%
Mark2014800011400002.0%
Mark20150500000.8%
Mark201550001800001.4%
Mark2015800011400002.0%