topic Re: Billing Vs Purchase in QlikView

Billing Vs Purchase

Sat, 26 Dec 2015 11:10:54 GMT

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.

Re: Billing Vs Purchase

nagarjuna_kotha — Sat, 26 Dec 2015 11:25:21 GMT

Hi ,

Just use LEFT JOIN between T1 and T2.

you will get ur result ?

Regards,

Nagarjuna

Re: Billing Vs Purchase

Sat, 26 Dec 2015 12:04:29 GMT

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

Re: Billing Vs Purchase

tiago_hubner — Sat, 26 Dec 2015 12:32:09 GMT

Hi,

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

See attached.

Re: Billing Vs Purchase

HirisH_V7 — Sat, 26 Dec 2015 12:37:52 GMT

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

Re: Billing Vs Purchase

Sat, 26 Dec 2015 12:48:19 GMT

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

Re: Billing Vs Purchase

Kushal_Chawda — Sat, 26 Dec 2015 13:14:11 GMT

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

Re: Billing Vs Purchase

Kushal_Chawda — Sat, 26 Dec 2015 13:54:16 GMT

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