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
jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach some sample file? 

Try like this

Dimension : Name, Year

Expression:

If(Sum(Sales) > 150001, 0.025,

     If(Sum(Sales) >= 100001, 0.02,

     If(Sum(Sales) >= 50001,  0.015,

      If(Sum(Sales) >= 0, 0.01))))

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

hi jagan ,

plz check the above table slap may varies i need dynamic expression ,

-lohi

Not applicable
Author

Hi ,

PFA QVW . its contain example of dynamic bucketing. Hope that may help u .

Thanks!!

Not applicable
Author

be practical in i have millions of users i have to create million groups , it is not possible

- lohi

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this in script

Data:

LOAD

*,

If(Sales > 150001, '>$150001',

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

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

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

LOAD

     Name,

     Year,

     Sum(Sales) AS Sales

FROM DataSource

Group by Name,  Year;

LEFT JOIN

LOAD

Name,

Year,

Slab,

Commission

FROM CommissionSource;


Hope this helps you.


Regards,

Jagan.

Not applicable
Author

hi jagan ,

hope it helps but

Peter

2014

$0-$50000

1%

john 2014$0-$50000

1.5%

if this the requirement the

peter will get 1%

john also get 1%

-lohi

jagan
Luminary Alumni
Luminary Alumni

Hi,

It should not happen, because we are join the Commission table based on Name, Year and Slab, so you will get different values for Name.

Check once again.

Regards,

jagan.

Not applicable
Author

hi jagan ,

i have tried like this

Commision:

LOAD*,

   

    if(CumulativeSum<=50000,'1%',if(CumulativeSum>=50001 and CumulativeSum<=100000,'1.5%',if(CumulativeSum>=100001 and CumulativeSum <=150000,'2%',if(CumulativeSum>=150001,'2.5%','NULL')))) As Comm

Resident CumulativeSum

Order by Name,Year

;

Comm:

LOAD * Resident Commision Order by Commision.Name,Commision.Year;

   

Commision1:

LOAD     Commision.Name,   

        Commision.Date,

        Commision.Year,

        (Commision.Comm*Commision.CumulativeSum) as Cumm   

        

Resident Commision   

Order by Commision.Name,Commision.Year

;

but the problem is slap may varies depends on Users.

- lohi

Not applicable
Author

hi jagan ,

i am forwarding the attachment

PFA ,

-lohi