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,

[giakoum]

firstsortedvalue function does that

"the first one" you mean based on some date I guess

firstsortedvalue([Payment Method],  -DateField)

[sprqlikview]

I've tried that but I get the error Invalid expression.

left join

LOAD [Coupon ID],

     firstsortedvalue(PaymentMethod, DATE) as PaymentMethod

[giakoum]

I would need a sample application to answer that I am afraid

[giakoum]

see attached

[vikramv]

Please see this.

http://community.qlik.com/thread/30532

Regards,

Vikky.

[sprqlikview]

Thanks for your help.

[sunny_talwar]

Please check if this works:

Table:

LOAD * INLINE [

    Coupon, Payment Method, Date

    1104, Cash, 02/02/2015

    1105, MASTERCARD, 01/31/2015

    1105, Cash, 12/31/2014

    1106, VISA, 11/30/2014

];

Right Join (Table)

LOAD FirstSortedValue(Coupon, -Date) as Coupon,

  FirstSortedValue([Payment Method], -Date) as [Payment Method],

  FirstSortedValue(Date, -Date) as Date

Resident Table

Group By Coupon;

Best,

S

[sprqlikview]

What would it look like with the following data?

I would always need the PaymentMethod of the highest Revenue.

Therefore for 8447 I would need Payment Method 351 MrT and a Total Revenue of 17.

LOAD * INLINE [

    Date, Shop, Coupon, PaymentMethod, Revenue

    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

    2015-01-19, 3020, 8455, Visa, 7

    2015-01-20, 3010, 4627, Amexco, 15.4

    2015-01-20, 3010, 4627, Cash, 10.4

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

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

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

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

];

[#10]

Hi,

PFA document

HTH