topic How to get same vendor name... in QlikView

How to get same vendor name...

surendraj — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

I have 4 fields.(sup_key,ven_key,ven_name,sale).

1.My scenario is when sup_key=ven_key,The coresponding ven_name should be obtained.

2.when ven_key<>sup_key,the above same ven_name should be obtained for the all the sup_key and ven_key.

Please find the attached snaps, with my input data and desired output.

input:

Out put should be

---Surendra

Re: How to get same vendor name...

vinieme12 — Tue, 31 Jan 2017 10:27:22 GMT

Try

LOAD *,

if(sup_key=ven_key,ven_name,if(sup_key=Previous(sup_key),peek(new_ven_name),ven_name)) as new_ven_name

;

LOAD *

INLINE [

sup_key, ven_key, ven_name, sale

120, 120, rwq, 225

120, 135, ewe, 111

120, 135, ewe, 534

135, 135, ped, 706

135, 215, eur, 831

135, 215, eur, 275

135, 215, eur, 132

135, 215, eur, 346

140, 144, vvv, 566

140, 140, gfd, 480

140, 154, mkl, 964

140, 154, mkl, 616

140, 154, mkl, 154

];

Re: How to get same vendor name...

surendraj — Tue, 31 Jan 2017 10:58:41 GMT

Thanks for reply.It works for sup_keys 120 and 135..

Its not working for 140.

Here i want new_ven_name as gfd for sup_key=140

Re: How to get same vendor name...

omkarvamsi — Tue, 31 Jan 2017 11:04:47 GMT

Hi Surendra,

Please try with below logic

maptoven_name:

Mapping

LOAD sup_key,

ven_name

FROM

(ooxml, embedded labels, table is Sheet2)

where sup_key=ven_key;

maindata:

LOAD sup_key,

ven_key,

ApplyMap('maptoven_name',sup_key,ven_name) as MapVenName,

sale

FROM

(ooxml, embedded labels, table is Sheet2);

Re: How to get same vendor name...

adamdavi3s — Tue, 31 Jan 2017 11:04:59 GMT

LOAD *,

if(sup_key=ven_key,ven_name,if(sup_key=Previous(sup_key),previous(ven_name),ven_name)) as new_ven_name

;

LOAD *

INLINE [

sup_key, ven_key, ven_name, sale

120, 120, rwq, 225

120, 135, ewe, 111

120, 135, ewe, 534

135, 135, ped, 706

135, 215, eur, 831

135, 215, eur, 275

135, 215, eur, 132

135, 215, eur, 346

140, 144, vvv, 566

140, 140, gfd, 480

140, 154, mkl, 964

140, 154, mkl, 616

140, 154, mkl, 154

];

Re: How to get same vendor name...

vinieme12 — Tue, 31 Jan 2017 11:08:05 GMT

Check your Original Post the value 140(sup) and 144(ven) is before the row when both keys match the output matches the original table you posted..

please check again

Re: How to get same vendor name...

vinieme12 — Tue, 31 Jan 2017 11:09:03 GMT

See the same output in table viewer (CTRL+T),, table box will only sort on one dimension

Re: How to get same vendor name...

vinieme12 — Tue, 31 Jan 2017 11:09:31 GMT

if you still have doubts post an excel sample!!

Re: How to get same vendor name...

adamdavi3s — Tue, 31 Jan 2017 11:17:57 GMT

This is the issue with using previous without an order by, but I can't see anyway you could order this simply to ensure the correct result.

Omkar's solution above is probably the most robust....

Re: How to get same vendor name...

surendraj — Tue, 31 Jan 2017 11:18:49 GMT

Hi Vineeth,

yes it will be like that only in some cases,
but I need the ven_name only when ever my sup_key=ven_key.That perticular ven_name is applicable to all rows for 140

In case of 140,the ven_name is gfd.so I want gfd for all rows..

Re: How to get same vendor name...

vinieme12 — Tue, 31 Jan 2017 11:21:44 GMT

then you should use Order by on a resident table

Re: How to get same vendor name...

surendraj — Tue, 31 Jan 2017 11:21:54 GMT

Yes its..ok..but I am waiting for another option.!!

Re: How to get same vendor name...

surendraj — Tue, 31 Jan 2017 11:23:22 GMT

I tried..but its not working for above sup_keys..!!

Re: How to get same vendor name...

vinieme12 — Tue, 31 Jan 2017 11:24:28 GMT

As Below, next time please post accurate output!!

temp:

LOAD *

INLINE [

sup_key, ven_key, ven_name, sale

120, 120, rwq, 225

120, 135, ewe, 111

120, 135, ewe, 534

135, 135, ped, 706

135, 215, eur, 831

135, 215, eur, 275

135, 215, eur, 132

135, 215, eur, 346

140, 144, vvv, 566

140, 140, gfd, 480

140, 154, mkl, 964

140, 154, mkl, 616

140, 154, mkl, 154

];

NoConcatenate

FACT:

LOAD *,

if(sup_key=ven_key,ven_name,if(sup_key=Previous(sup_key),peek(new_ven_name),ven_name)) as new_ven_name

Resident temp

Order by sup_key,ven_key;

Drop Table temp;

Re: How to get same vendor name...

adamdavi3s — Tue, 31 Jan 2017 11:30:26 GMT

I considered that as an order by but this assumes that the lowest ven_key is the one which matches the sup key as well. I think it has to be something like:

temp:

LOAD *, if(sup_key = ven_key,0,1) as matchflag;

LOAD *

INLINE [

sup_key, ven_key, ven_name, sale

120, 120, rwq, 225

120, 135, ewe, 111

120, 135, ewe, 534

135, 135, ped, 706

135, 215, eur, 831

135, 215, eur, 275

135, 215, eur, 132

135, 215, eur, 346

140, 144, vvv, 566

140, 140, gfd, 480

140, 154, mkl, 964

140, 154, mkl, 616

140, 154, mkl, 154

];

NoConcatenate

FACT:

LOAD *,

if(sup_key=ven_key,ven_name,if(sup_key=Previous(sup_key),peek(new_ven_name),ven_name)) as new_ven_name

Resident temp

Order by sup_key,matchflag;

Drop Table temp;

Re: How to get same vendor name...

surendraj — Tue, 31 Jan 2017 11:35:52 GMT

I posted accurately.In some cases sup_key=ven_key are matched either 2nd row or 3rd row.(not only first row).I want that matched row ven_name for all the rows.

Now its working fine..!!

Thank you so much!!