topic Re: Multiplen names - only one in QlikView

Multiplen names - only one

sprqlikview — Fri, 20 Nov 2020 18:02:30 GMT

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,

Re: Multiplen names - only one

giakoum — Fri, 06 Feb 2015 13:51:24 GMT

firstsortedvalue function does that

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

firstsortedvalue([Payment Method], -DateField)

Re: Multiplen names - only one

sprqlikview — Fri, 06 Feb 2015 14:45:10 GMT

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

left join

LOAD [Coupon ID],

firstsortedvalue(PaymentMethod, DATE) as PaymentMethod

Re: Multiplen names - only one

giakoum — Fri, 06 Feb 2015 14:46:17 GMT

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

Re: Multiplen names - only one

giakoum — Fri, 06 Feb 2015 14:56:44 GMT

see attached

Re: Multiplen names - only one

vikramv — Fri, 06 Feb 2015 14:57:05 GMT

Please see this.

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

Regards,

Vikky.

Re: Multiplen names - only one

sprqlikview — Fri, 06 Feb 2015 14:59:54 GMT

Thanks for your help.

Re: Multiplen names - only one

sunny_talwar — Fri, 06 Feb 2015 15:01:28 GMT

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,

Re: Multiplen names - only one

sprqlikview — Wed, 11 Feb 2015 10:00:15 GMT

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

];

Re: Multiplen names - only one

Wed, 11 Feb 2015 11:21:33 GMT

Hi,

PFA document

HTH

Re: Multiplen names - only one

sprqlikview — Wed, 11 Feb 2015 12:09:50 GMT

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.

Re: Multiplen names - only one

Thu, 12 Feb 2015 06:08:55 GMT

Hi,

PFA

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

I hope you need this.

Amay

Re: Multiplen names - only one

sprqlikview — Thu, 12 Feb 2015 07:21:15 GMT

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

Re: Multiplen names - only one

Thu, 12 Feb 2015 08:45:16 GMT

Hi,

I have updated it agn as you mentioned.

let me know..:)

Amay.