Multiplen names - only one

[sprqlikview]

I have for one Coupon more than 1 Payment method I'm however only interested in the first one. I have this in a table and I try to left Join it to my coupon details.

In Excel I would use vlookup and only get one result. In QlikView I get 2 and therefore it multiplies my result.

E.g.

Coupon	Payment Method
1104	Cash
1105	MASTERCARD
1105	CASH
1106	VISA

From this I should get the following:

Coupon	Payment Method
1104	Cash
1105	MASTERCARD
1106	VISA

I guess this can be solved easily but I don't know why.

Thanks,

[sprqlikview]

Thanks a lot for this. Has helped me to understand the FirstSortedValue.

The problem is however that I have the same coupon number on several days and possibly in multiple shops on the same day and then I get the same S_R for the tickets.

E.g. If I changed the last 4 lines to having the same Ticket ID

    2015-01-20, 3010, 4639, 350 MrP, 44.1

    2015-01-20, 3010, 4639, Cash, 16.3

    2015-01-20, 3020, 4639, Lunch Check, 18

    2015-01-20, 3020, 4639, Amexco, 13.4

I would get for both entries the same PaymentMethod and S_R.

I've tried to make a unique group by e.g. Date & Shop & Ticket.

[#3]

Hi,

PFA

I have changed the code a bit according to your req.

I hope you need this.

Amay

[sprqlikview]

Almost there   Really appreciate your support.

Date, Shop, Coupon and Revenue is correct however PaymentMethod still not.

I see two Problems:

1) Payment Method is not always the one with the top Amount

2) Payment Method when Coupon exists several times - it takes the same Payment method for both coupons

Examples:

1) Coupon 8447 is correctly with 351 MrT

Coupon 8448 Should have Cash as the Cash amount is 16.6

    2015-01-19, 3020, 8447, Cash, 7

    2015-01-19, 3020, 8447, 351 MrT, 10

    2015-01-19, 3020, 8448, Cash, 16.6

    2015-01-19, 3020, 8448, Visa, 9.5

2) For Shop 3020 Payment Method should be Lunch Check and for 3010 350 MrP.

    2015-01-20, 3010, 4639, 350 MrP, 44.1

    2015-01-20, 3010, 4639, Cash, 16.3

    2015-01-20, 3020, 4639, Lunch Check, 18

    2015-01-20, 3020, 4639, Amexco, 13.4

[#5 · Accepted solution]

Hi,

I have updated it agn as you mentioned.

let me know..:)

Amay.