topic Re: How to Concise Duplicate Value in QlikView

How to Concise Duplicate Value

Fri, 30 Sep 2011 03:30:32 GMT

Hi,

I got a table with same TicketNumber value but inside contain different value for each TicketNumber such as Station and Amount as following :

TicketNumber Station Amount

1 E1 0

1 K4 3

1 A2 3

2 E2 0

....

How can i going to concise TicketNumber into single record? as following

Ticket Number EnterStation ExitStation Amount

1 E1 A2 3

sridhar240784 — Fri, 30 Sep 2011 03:59:21 GMT

Jeffrey,

You can do this using FirsValue() and LastValue() Function in QlikVeiw.

Your Script should be some thing like below one.

Test:

Load * inline [

TicketNumber, Station, Amount

1, E1, 0

1, K4, 3

1, A2, 3

2, E2, 0

2, K5, 6

2, A2, 6

];

Inner Join

Load TicketNumber,

FirstValue(Station) as EntryStation,

LastValue(Station) as Station,

LastValue(Station) as ExitStation

Resident Test Group By TicketNumber;

Have a look at the attached application.

Hope this helps you.

-Sridhar