topic Re: How to do this in QlikView

How to do this

ecabanas — Fri, 12 Jun 2015 10:07:32 GMT

I have a table with the product cost(A) and another table with the cost from invoices(B).

I would like to find from the A table the item cost and find the last invoice cost from B table

Anyone knows how to do this?

Many thank's

Eduard

Re: How to do this

Fri, 12 Jun 2015 10:19:23 GMT

Hi,

Could you please elaborate your requirement.

Re: How to do this

Anonymous — Fri, 12 Jun 2015 10:23:39 GMT

Hi ,

So what I understand you want to get the invoice from Table B, with same cost in Table A (Latest Date)

Then the last Cost 175 should attach with 1111 invoice no. correct ??

or you are considering any other logic ??

Thanks

BKC

Re: How to do this

Fri, 12 Jun 2015 10:38:48 GMT

Hi,

By reading your query what I can understand is you have to map invoice of table B to Table 1having the same cost & same ItemID.Just applymap it if that the issue.But i guess the requirement is something else.

Re: How to do this

ecabanas — Fri, 12 Jun 2015 10:43:01 GMT

Hi Balkumar,

I just added the example. I would like to add a for every fiel from A table the lattest invoice cost for each row.

Many thank's

Re: How to do this

Joaquin_Lazaro — Fri, 12 Jun 2015 10:59:58 GMT

Hi Eduard:

RealCost:

Load ....

Trim(ItemID) & '+' & Cost as KeyRC

InoviceCost:

Left join (RealCost)

Load ....

Trim(ItemID) & '+' & Cost as KeyIC

You should manage the field names in order to avoid syntetic keys !!!

Hope this helps you.

Joaquín

Re: How to do this

Joaquin_Lazaro — Fri, 12 Jun 2015 11:25:26 GMT

Hi Eduard:

Look at the attached app.

But .... If the real cost fo AA 01/03/2015 is 1.75 the invoice is 1111

Regards

Joaquín

Re: How to do this

ecabanas — Fri, 12 Jun 2015 11:36:18 GMT

Hi Joaquin,

I attached your answer, but did not solve the problem.

For example the second line in cost table is linked with invoice line "11111" and has to be linked to invoice "4444"

Many thank's for your patience

Eduard

Re: How to do this

Joaquin_Lazaro — Fri, 12 Jun 2015 11:45:10 GMT

Hi Eduard:

No arrow for the 4444 invoice !!!!

The second line in the real cost line has an arrow to the 3333 invoice.

Do I miss anything ?

Joaquín

Re: How to do this

ecabanas — Fri, 12 Jun 2015 11:59:56 GMT

SOrry Joaquin, you are right, my falt!!

MAAANY thank's for your help

Regards

Eduard

Re: How to do this

Joaquin_Lazaro — Fri, 12 Jun 2015 12:01:36 GMT

You're welcome.

Think about what will happen if two prices repeat over the time !!!!!

Joaquín

Re: How to do this

wizardo — Fri, 12 Jun 2015 12:05:59 GMT

!@if i understood what you need is to find for each item, the last invice of that item and get the cost from that invoice and then have the new cost show next to the cost from the items table

try this:

Temp_ItemCosts:

LOAD * INLINE [

Item, DateCost, ItemCost

AA, 15/05/2015, 2.00

AA, 20/04/2015, 2.15

AA, 01/03/2015, 1.75

];

Temp_InvoiceCosts:

LOAD * INLINE [

Invoice, Item, Date, CostInvoice

5555, AA, 30/04/2015, 2.00

4444, AA, 28/04/2015, 3.00

3333, AA, 15/04/2015, 2.15

2222, AA, 05/02/2015, 1.80

1111, AA, 31/12/2014, 1.75

];

Temp_InvoiceCosts1:

LOAD

Invoice,

Item,

Date as FromDate,

CostInvoice,

IF(IsNull(PEEK('Item')) or PEEK('Item') <> Item,Date(Today()),Date(peek('FromDate')-1)) as ToDate

Resident Temp_InvoiceCosts

Order By Item,Date desc;

DROP Table Temp_InvoiceCosts;

left join

IntervalMatch (DateCost,Item) load FromDate, ToDate,Item resident Temp_InvoiceCosts1;

Right Join

LOAD

Resident Temp_ItemCosts;

DROP Table Temp_ItemCosts;

Re: How to do this

christian77 — Fri, 12 Jun 2015 12:08:24 GMT

I think the 3rd arrow should go to 1111.

Anyway. What's that?

you can't relate data by cost. what if 2 items have the same cost?

seeing is believing

Re: How to do this

wizardo — Fri, 12 Jun 2015 12:12:42 GMT

i think what he wants was t ofind the last invoce and take the cost from there

not to link the table by cost

Re: How to do this

ecabanas — Fri, 12 Jun 2015 13:12:26 GMT

Hi Daniel,

Yes that's what I'm looking for, and I tryied to follow your code and there is a problem and sorry, I could not find it:

""Field not found

left join

IntervalMatch (INVOICEDATE,ITEMID) load FromDate, ToDate,ITEMID resident VENDINVOICETRANS1"

//Temp_ItemCosts:

//LOAD * INLINE [

//Item, DateCost, ItemCost

//AA, 15/05/2015, 2.00

//AA, 20/04/2015, 2.15

//AA, 01/03/2015, 1.75

//];

INVENTTRANS:

LOAD * from $(VStore)INVENTTRANS.qvd (qvd);

VENDINVOICETRANS:

LOAD ITEMID AS ITEMID_WAC,

Trim(ITEMID)&'+'&unit_price as KeyRC,

SQL Select * From AX_SQL_VENDINVOICETRANS;

//Temp_InvoiceCosts:

//LOAD * INLINE [

//Invoice, Item, Date, CostInvoice

//5555, AA, 30/04/2015, 2.00

//4444, AA, 28/04/2015, 3.00

//3333, AA, 15/04/2015, 2.15

//2222, AA, 05/02/2015, 1.80

//1111, AA, 31/12/2014, 1.75

//];

VENDINVOICETRANS1:

LOAD

INVOICEID,

ITEMID,

INVOICEDATE as FromDate,

unit_price,

IF(IsNull(PEEK('ITEMID')) or PEEK('ITEMID') <> ITEMID,Date(Today()),Date(peek('FromDate')-1)) as ToDate

Resident VENDINVOICETRANS

Order By ITEMID,INVOICEDATE desc;

DROP Table VENDINVOICETRANS;

left join

IntervalMatch (INVOICEDATE,ITEMID) load FromDate, ToDate,ITEMID resident VENDINVOICETRANS1;

Right Join

LOAD

Resident INVENTTRANS;

DROP Table Temp_ItemCosts;.

Many many thank's

Re: How to do this

wizardo — Fri, 12 Jun 2015 13:19:08 GMT

i dont know what you have in your qvd table

the fields "INVOICEDATE" and "ITEMID" must be in the table --> INVENTTRANS.qvd

Re: How to do this

Joaquin_Lazaro — Fri, 12 Jun 2015 14:03:23 GMT

HI Eduard:

Here you have the document with the dates related by an interval match.

Regards

Joaquín

Re: How to do this

ecabanas — Fri, 12 Jun 2015 15:26:28 GMT

Hi Joaquin

Still with problems, I applied your script to my data model and i could not match the info, there are 2 syn and the data is not linked, could you take a view?

Many thank's and sorry for the terrible headache

Regards

Re: How to do this

Joaquin_Lazaro — Mon, 15 Jun 2015 08:34:12 GMT

Hi Eduard:

The syntetic key is correct and you should keep it, read this IntervalMatch

I'm having a look to your app.

Regards

Joaquín

Re: How to do this

Joaquin_Lazaro — Mon, 15 Jun 2015 08:49:11 GMT

Hi:

Temp_RealCosts:

LOAD * from $(VStore)INVENTTRANS.qvd (qvd);

// This line in the previous LOAD

DATE(DATEFINANCIAL_LINE, 'YYYY/MM/DD') as DATEFINANCIAL_LINE,

RealCosts:

LOAD ITEMID,

IF (Not IsNUll(Previous(Cost_WAC)), Date(Previous(DATEFINANCIAL_LINE) + 1, 'YYYY/MM/DD'), Date('2011/01/01', 'YYYY/MM/DD')) as DateCostFrom, DATE(DateCostFrom, 'YYYY/MM/DD') as DateCostFrom,

Cost_WAC as RealCost

Resident Temp_RealCosts

Order by DATEFINANCIAL_LINE;

Drop Table Temp_RealCosts;

InvoiceCosts:

LOAD INVOICEID AS Invoice,

ITEMID as Item,

DATE(INVOICEDATE, 'YYYY/MM/DD') as Date,

unit_price as Cost_Invoice;

SQL Select * From AX_SQL_VENDINVOICETRANS;

IntervalMatch:

IntervalMatch (Date)

Load distinct DateCostFrom, DATEFINANCIAL_LINE resident RealCosts;

I guess your dates don't match beacuse they're in different formats

See you soon

Joaquín