scripting

Mon, 15 Sep 2014 16:08:18 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

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%

Re: scripting

Anonymous — Mon, 15 Sep 2014 20:11:03 GMT

hi,

try this:

CumulativeSum:

NoConcatenate

LOAD

Name,

Date,

Year,

Sales,

Name & '|' & Year as %NameYear,

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

Resident Temp1;

given your Table above then write:

intervalls:

Intervallmatch(CumulativeSum, %NameYear)

load*

;

load

Start,

End,

Name & '|' & as %NameYear,

Commission

Resident myintervalltable; //this is the table you posted above

left join (CumulativeSum)

%NameYear,

Commission

Resident intervalls;

drop table intervalls;

This will however only work when you only have one Value for CumulativeSum per every Name/Year.

Best

Stefan

topic Re: scripting in QlikView

scripting

Re: scripting