topic Duplicate tickets flag calculation in QlikView

Duplicate tickets flag calculation

Anonymous — Wed, 06 Apr 2016 08:29:40 GMT

Hi Friends,

I have some fields like Branch, Case,Down_since and Up_Since, now my issue is multiple tickets have same Up_since in a branch. I need to captured same Up_since tickets as flag 1 and minimum down_since for that tickets should be flag as 0, I'm attaching one excel file in that one Output column is there i'm expecting that...Please help me friends.

Re: Duplicate tickets flag calculation

Kushal_Chawda — Wed, 06 Apr 2016 10:26:00 GMT

I am not sure how you are getting the output. Can you explain the logic

Re: Duplicate tickets flag calculation

sunny_talwar — Wed, 06 Apr 2016 10:30:25 GMT

I was going to ask the same thing. I am confused

Why are there two 0's in the screenshot

Re: Duplicate tickets flag calculation

luciancotea — Wed, 06 Apr 2016 10:37:24 GMT

When you load the data in the script, order by Up_Since and Down_Since DESC and use peek() to see if the previous row have the same Up_Since, like:

TABLE2:

LOAD *, if(Branch = peek('Branch') and Up_Since = peek('Up_Since'), 1, 0) as Flag

RESIDENT TABLE1

ORDER BY Up_Since, Down_Since DESC, Branch

DROP TABLE1;

Re: Duplicate tickets flag calculation

Anonymous — Wed, 06 Apr 2016 11:11:02 GMT

Sorry Sunny,

In excel that output column we manually created.Inside Blue colour tickets are duplicate tickets in that red colour ticket is original ticket based that is minimum down_since when compare to all duplicate tickets.