Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Billing Vs Purchase

Capture.PNG

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.

7 Replies
nagarjuna_kotha
Partner
Partner

Hi ,

Just use LEFT JOIN between T1 and T2.

you will get ur result ?

Regards,

Nagarjuna

Not applicable

if you didn't get my problem then don't post the silly answer

tiago_hubner
Partner
Partner

!

Hi,

Do you will need to create a compose key with Material, Plant and Dates.

See  attached.

HirisH_V7
Master
Master

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:

table left join.PNG

Hope this is what you required.

Thanks,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Not applicable

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

Kush
MVP
MVP

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;


Kush
MVP
MVP

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