topic Re: Manual data iteration problem in load script. in QlikView

Manual data iteration problem in load script.

Thu, 18 Jun 2015 12:49:05 GMT

Hello!

I am having a problem with ranking data as per my needs.

I tried in SQL directly and with use of QV functions.

I want to achieve rank as per below example

Employee	Type	Week	Rank
A	1	10	1
A	1	11	1
A	2	12	2
A	1	13	3
B	3	10	1
B	2	11	2
B	3	12	3
B	2	13	4
B	2	14	4

1. Rank should reset for next employee (row 5)

2. When Type is the same continuously, week by week, Rank stays the same. (rows 1 & 2)

2. When Type changes, rank changes as well (row 3)

3. When same Employee and Type combination is occurring again, but not as continuation, Rank must change (row 2 & 4, as was divided by different type in row 3

How to achieve that?

Regards!

Re: Manual data iteration problem in load script.

Gysbert_Wassenaar — Thu, 18 Jun 2015 13:24:44 GMT

Try:

LOAD

Employee,

Type,

Week,

if(Previous(Employee)=Employee,if(Previous(Type)=Type,peek(Rank),rangesum(1,peek(Rank))),1) as Rank

FROM ...

Re: Manual data iteration problem in load script.

Kushal_Chawda — Thu, 18 Jun 2015 13:25:44 GMT

Please see the attached file

Re: Manual data iteration problem in load script.

Fri, 19 Jun 2015 06:37:12 GMT

Big thanks to both of you! Both solutions are correct Marking Gysbert's one as correct as he was faster.

Re: Manual data iteration problem in load script.

Kushal_Chawda — Fri, 19 Jun 2015 06:39:11 GMT

Yeah gwassenaar‌ is the champ.