How to get same vendor name...

[surendraj]

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

[vinieme12]

then you should use Order by on a resident table

[surendraj]

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

[surendraj]

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

[vinieme12 · Accepted solution]

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;

[adamdavi3s]

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;

[surendraj]

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!!