Dynamic scripting

[#1]

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

[jagan]

Hi,

Try Advanced Interval match (Extended Syntax) topic in Qlikview Help file.

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

LEFT JOIN IntervalMatch (CumulativeSum, Name, Year)

LOAD

     Start,

     End,

     Name,

     Year

resident Key;

Regards,

Jagan.