Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys,
I have Two tables T1 & T2 from both tables i wanna output like data has given in O/P table.
Means For a material purchase date must be immediate preceding purchase date than billing date.
Hi ,
Just use LEFT JOIN between T1 and T2.
you will get ur result ?
Regards,
Nagarjuna
if you didn't get my problem then don't post the silly answer
!
Hi,
Do you will need to create a compose key with Material, Plant and Dates.
See attached.
Hi,
T1:
LOAD * INLINE [
Material,Plant,BillingDate
A,A01,01/01/2012
A,A01,05/01/2012
A,A02,03/01/2012
A,A02,10/01/2012
];
Left join(T1)
T2:
LOAD * INLINE [
Material,Plant,PurchasingDate,Quantity
A,A01,03/01/2012,Q1
A,A01,30/12/2011,Q2
A,A02,04/01/2012,Q3
A,A02,08/01/2012,Q4
];
NoConcatenate
New:
Load Material,
Plant,
BillingDate,
PurchasingDate,
If(PurchasingDate<BillingDate,PurchasingDate) as PurchaseDate,
Quantity
Resident T1 ;
Drop Table T1;
Output:
Hope this is what you required.
Thanks,
Hirish
thanks for your co-ordination but what if the billing date 01-01-2012 and purchasing dates are 30-12-2011 and 31-12-2011.
Means from your logic it will give both purchase date while i wanna immediate purchase date (31-12-2011).
T2:
LOAD Material, Plant,Quantity, date(Date#(Purchase_Date,'DD-MM-YYYY'),'DD-MM-YYYY') as Purchase_Date Inline [
Material, Plant, Purchase_Date, Quantity
A,A01,03-01-2012,Q1
A,A01,30-12-2011,Q2
A,A01,30-12-2011,Q2
A,A02,04-01-2012,Q3
A,A02,08-01-2012 ,Q4];
T2_1:
NoConcatenate
LOAD Material, Plant,
date(min(Purchase_Date),'DD-MM-YYYY') as MinPurchaseDate,
date(max(Purchase_Date),'DD-MM-YYYY') as MaxPurchaseDate
Resident T2
Group by Material, Plant;
Left Join(T2_1)
LOAD Distinct Material, Plant, Purchase_Date as MaxPurchaseDate, Quantity as MaxQty
Resident T2;
Left Join(T2_1)
LOAD Distinct Material, Plant, Purchase_Date as MinPurchaseDate, Quantity as MinQty
Resident T2;
DROP Table T2;
T1:
LOAD Material, Plant,date(Date#(Billing_Date,'DD-MM-YYYY'),'DD-MM-YYYY') as Billing_Date Inline [
Material, Plant, Billing_Date
A,A01,01-01-2012
A,A01,05-01-2012
A,A02,03-01-2012
A,A02,10-01-2012 ];
Left Join(T1)
LOAD Distinct Material, Plant,MinPurchaseDate,MinQty,MaxPurchaseDate,MaxQty
Resident T2_1;
DROP Table T2_1;
T1_1:
NoConcatenate
LOAD *,
if(MaxPurchaseDate<Billing_Date,MaxPurchaseDate,
if(MinPurchaseDate<Billing_Date,MinPurchaseDate,'NULL')) as PURCHASE_DATE,
if(MaxPurchaseDate<Billing_Date,MaxQty,
if(MinPurchaseDate<Billing_Date,MinQty,'NULL')) as QUANTITY
Resident T1;
DROP Table T1;
DROP Fields MinPurchaseDate,MaxPurchaseDate,MaxQty,MinQty;
I would not suggest to join both the tables in script directly as It will be many to many join. Script will be hanged if the amount of data is large