topic Re: Re: Dynamic scripting in QlikView

Dynamic scripting

Wed, 10 Sep 2014 09:15:51 GMT

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

peter

2014

$50001-$100000

1.5%

peter

2014

$100001-$150000

peter

2014

>$150001

2.5%

john

2014

$0-$50000

1.5%

like this the commision varies plz help

-lohi

Re: Dynamic scripting

jagan — Wed, 10 Sep 2014 09:24:02 GMT

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.

Re: Dynamic scripting

Wed, 10 Sep 2014 09:25:38 GMT

hi jagan ,

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

-lohi

Re: Re: Dynamic scripting

Wed, 10 Sep 2014 09:34:00 GMT

Hi ,

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

Thanks!!

Re: Re: Dynamic scripting

Wed, 10 Sep 2014 09:38:47 GMT

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

- lohi

Re: Dynamic scripting

jagan — Wed, 10 Sep 2014 09:43:27 GMT

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.

Re: Dynamic scripting

Wed, 10 Sep 2014 09:50:15 GMT

hi jagan ,

hope it helps but

Peter

2014

$0-$50000

john

2014

$0-$50000

1.5%

if this the requirement the

peter will get 1%

john also get 1%

-lohi

Re: Dynamic scripting

jagan — Wed, 10 Sep 2014 09:57:20 GMT

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.

Re: Dynamic scripting

Wed, 10 Sep 2014 09:57:46 GMT

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

Re: Re: Dynamic scripting

Wed, 10 Sep 2014 10:13:26 GMT

hi jagan ,

i am forwarding the attachment

PFA ,

-lohi

Re: Re: Dynamic scripting

Wed, 10 Sep 2014 10:36:00 GMT

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

Re: Re: Dynamic scripting

Wed, 10 Sep 2014 13:26:43 GMT

hi QV's ,

Any idea on this dynamic content,

-lohi

Re: Dynamic scripting

jagan — Thu, 11 Sep 2014 03:00:28 GMT

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.

Re: Dynamic scripting

Thu, 11 Sep 2014 08:01:40 GMT

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

Re: Dynamic scripting

jagan — Thu, 11 Sep 2014 08:33:27 GMT

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.

Re: Dynamic scripting

Thu, 11 Sep 2014 09:42:23 GMT

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

Re: Dynamic scripting

jagan — Thu, 11 Sep 2014 09:49:13 GMT

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.

Re: Re: Dynamic scripting

Thu, 11 Sep 2014 10:08:46 GMT

hi jagan ,

Please find attachment

- lohi

Re: Dynamic scripting

Thu, 11 Sep 2014 10:48:07 GMT

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

Re: Dynamic scripting

Mon, 15 Sep 2014 15:59:13 GMT

hi jagan ,

If data is like this how to do ,

Name	Year	Start	End	Commission
Peter	2014	0	60000	1.0%
Peter	2014	60001	100000	1.5%
Peter	2014	100001	150000	2.0%
Peter	2015	0	50000	1.0%
Peter	2015	50001	100000	1.5%
Peter	2015	100001	150000	2.0%
Tom	2014	0	50000	1.2%
Tom	2014	50001	100000	1.6%
Tom	2014	100001	150000	2.5%
Tom	2015	0	50000	1.2%
Tom	2015	50001	100000	1.6%
Tom	2015	100001	150000	2.5%
Mark	2014	0	50000	0.8%
Mark	2014	50001	80000	1.4%
Mark	2014	80001	140000	2.0%
Mark	2015	0	50000	0.8%
Mark	2015	50001	80000	1.4%
Mark	2015	80001	140000	2.0%