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]

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

];

[surendraj]

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

[omkarvamsi]

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);

[adamdavi3s]

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

];

[vinieme12]

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

[vinieme12]

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

[vinieme12]

if you still have doubts post an excel sample!!

[adamdavi3s]

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

[surendraj]

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