Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Re: Dynamic scripting

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

Re: Dynamic scripting

hi jagan ,

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

-lohi

Not applicable

Re: Re: Dynamic scripting

Hi ,

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

Thanks!!

Not applicable

Re: Re: Dynamic scripting

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

- lohi

MVP
MVP

Re: Dynamic scripting

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

Re: Dynamic scripting

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

MVP
MVP

Re: Dynamic scripting

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

Re: Dynamic scripting

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

Re: Re: Dynamic scripting

hi jagan ,

i am forwarding the attachment

PFA ,

-lohi

Community Browser