topic Re: unique prod id in QlikView

unique prod id

Tue, 30 Jun 2015 03:35:45 GMT

Hi,

I do have requirement where need to calculate whether prod has been upgraded or downgraded based on three grading systems, the issue is if I prod has been upgraded or downgraded into ratings, i need to count only once.

for example conisder below data

Company	Product Id	Business date	Grade 1	Grade 2	Grade 3
Microsoft	A123	6/29/2015	10	10	10
Microsoft	A123	6/28/2015	9	9	10
Microsoft	A456	6/29/2015	10	10	10
Microsoft	A456	6/28/2015	10	10	11
Microsoft	A789	6/29/2015	13	12	12
Microsoft	A789	6/29/2015	12	12	12
Duke	B123	6/28/2015	9	12	13
Duke	B123	6/29/2015	9	12	14
Edison	B456	6/28/2015	11	13	13
Edison	B789	6/29/2015	9	9	12
Edison	B789	6/28/2015	10	10	9

logic for upgrades: if difference of previous grade-current grade>0, then upgrade

logic for downgrades: if difference of previous grade-current grade<0, then downgrade

as you can see in above example prod A123 of microsoft has been upgraded in both grade 1 and grade 2 and in grade 3 its unchanged,

so, if the product has been upgraded in at least one of grades it should count as only 1 upgrade, so in total Microsoft has two upgraded products A123(in grade 1 and grade2) and A 789, hence count is 2 and for Duke product has been downgraded in grade 3.

o/p table should be

Company	Total Upgrades	Total Downgrades
Microsoft	2	1
Duke	0	1
Edison	1	1

Can you please let me know the logic for this. Any help is highly appreciated.

Thanks,

Re: unique prod id

jagan — Tue, 30 Jun 2015 05:30:26 GMT

Hi,

Try this script

Temp:

LOAD

Company, [Product Id], Date([Business date]) AS [Business date], [Grade 1], [Grade 2], [Grade 3]

INLINE

[

Company, Product Id, Business date, Grade 1, Grade 2, Grade 3

Microsoft, A123, 6/29/2015, 10, 10, 10

Microsoft, A123, 6/28/2015, 9, 9, 10

Microsoft, A456, 6/29/2015, 10, 10, 10

Microsoft, A456, 6/28/2015, 10, 10, 11

Microsoft, A789, 6/29/2015, 13, 12, 12

Microsoft, A789, 6/29/2015, 12, 12, 12

Duke, B123, 6/28/2015, 9, 12, 13

Duke, B123, 6/29/2015, 9, 12, 14

Edison, B456, 6/28/2015, 11, 13, 13

Edison, B789, 6/29/2015, 9, 9, 12

Edison, B789, 6/28/2015, 10, 10, 9

];

Data:

LOAD

If(Peek(Company) = Company AND Peek([Product Id]) = [Product Id], If([Grade 1] > Peek([Grade 1]), 1, 0) + If([Grade 2] > Peek([Grade 2]), 1, 0) + If([Grade 3] > Peek([Grade 3]), 1, 0)) AS Upgrade,

If(Peek(Company) = Company AND Peek([Product Id]) = [Product Id], If([Grade 1] < Peek([Grade 1]), 1, 0) + If([Grade 2] < Peek([Grade 2]), 1, 0) + If([Grade 3] < Peek([Grade 3]), 1, 0)) AS Downgrade

RESIDENT Temp

ORDER BY Company, [Product Id], [Business date];

DROP TABLE Temp;

Now in chart use

Sum(Upgrade) For getting Upgrade count

Sum(Downgrade) For getting Downgrade count

Hope this helps you.

Regards,

Jagan.

Re: unique prod id

qlikviewwizard — Tue, 30 Jun 2015 07:07:46 GMT

Hi qkvw1234,

Please follow jagan‌ 's Advise. I used pivot table.

Enclosed the app for your reference.

Re: unique prod id

Tue, 30 Jun 2015 09:53:01 GMT

Hi Jagan,

can this be done in UI. ? also if solution if is at script level, i do have multiple dates how do I compare selected date and its previous date. please help..

Re: unique prod id

jagan — Tue, 30 Jun 2015 14:20:26 GMT

Hi,

It is very difficult to do this in front end, you have to do lot of complex expressions. The best thing is you can do this at script level.