Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Re: How to do this

You're welcome.

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

Joaquín

Highlighted
Creator III
Creator III

Re: How to do this

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

Highlighted
Partner
Partner

Re: How to do this

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

Highlighted
Creator III
Creator III

Re: How to do this

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

not to link the table by cost

Highlighted
Creator II
Creator II

Re: How to do this

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

Highlighted
Creator III
Creator III

Re: How to do this

i dont know what you have in your qvd table

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

Highlighted
Specialist II
Specialist II

Re: How to do this

HI Eduard:

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

Regards

Joaquín

Highlighted
Creator II
Creator II

Re: How to do this

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

Highlighted
Specialist II
Specialist II

Re: How to do this

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

Highlighted
Specialist II
Specialist II

Re: How to do this

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