Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
then you should use Order by on a resident table
Yes its..ok..but I am waiting for another option.!!
I tried..but its not working for above sup_keys..!!
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;
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;
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!!