Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

How to do this

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

Item_lastInvoice.png

Anyone knows how to do this?

Many thank's

Eduard

19 Replies
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

You're welcome.

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

Joaquín

wizardo
Creator III
Creator III

!@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;

christian77
Partner - Specialist
Partner - Specialist

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

wizardo
Creator III
Creator III

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

not to link the table by cost

ecabanas
Creator II
Creator II
Author

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

wizardo
Creator III
Creator III

i dont know what you have in your qvd table

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

JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

HI Eduard:

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

Regards

Joaquín

ecabanas
Creator II
Creator II
Author

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

JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

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

JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

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