topic Re: Re: Join Max Date in QlikView

Join Max Date

Thu, 27 Mar 2014 14:04:49 GMT

Hi!!

I've to join two table by the itemID and Sale/Purchase Date.

In this join I have to connect the ItemID from table Sale with the corresponding ItemID in the table Stock where the last PurshaseDate is <= SaleDate.

can someone help me?

Re: Join Max Date

Thu, 27 Mar 2014 16:14:25 GMT

Hi Fernando,

You would need a query as below:

SELECT sa.ItemID, sa.SaleDate, st2.PurchaseDate, sa.SalePrice, st2.ItemCost

FROM Sale sa

INNER JOIN (SELECT MAX(PurchaseDate) AS "MaxPurchaseDate", ItemID FROM Stock GROUP BY ItemID) st

ON sa.ItemID = st.ItemID

AND st.MaxPurchaseDate <= sa.SaleDate

INNER JOIN Stock st2

ON st2.ItemID = st.ItemID

AND st2.PurchaseDate = st.MaxPurchaseDate

Re: Join Max Date

Thu, 27 Mar 2014 16:24:15 GMT

Hi!!

My intention is do this in the LOAD using qlikview joins, not with a sql query.

but thank you for your attention

Re: Join Max Date

arsal_90 — Thu, 27 Mar 2014 16:35:59 GMT

can u provide the data in excel file as i can tell you how to perform through qlikview script

Re: Re: Join Max Date

Thu, 27 Mar 2014 16:49:21 GMT

Hi Muhammad!

Here is the file.

The tables are in the sheets.

Thxs!

Re: Join Max Date

fernando_tonial — Thu, 27 Mar 2014 16:50:53 GMT

You need join tables and after make a load resident with your where.

like this:

StockTable_TMP:
LOAD
    ItemID,
    PurchaseDate,
    ItemCost
From StockTable.QVD (qvd);

Join (StockTable_TMP)
LOAD
    ItemID,
    SaleDate,
    SalePrice
From Sale_Table.qvd (qvd);

Sale_Cost_Table:
NoConcatenate
LOAD
    ItemID,
    PurchaseDate,
    ItemCost,
    SaleDate,
    SalePrice
Resident StockTable_TMP
Where PurchaseDate <= SaleDate;

DROP Table StockTable_TMP;

Best Regards.

Tonial.

Re: Join Max Date

Thu, 27 Mar 2014 16:56:34 GMT

Opa!! Dae Fernando!

Se eu fizer dessa maneira, todos os registros menores ou iguais a SaleDate entrarão no join.

Minha intenção é fazer um Max no PurchaseDate, trazendo apenas o registro imediatamente menos ou igual a SaleDate.

Abraço!

Re: Re: Re: Join Max Date

arsal_90 — Thu, 27 Mar 2014 17:06:54 GMT

Hi Fernando

Please find the attached file. If this is not your desired result then let me know

Re: Join Max Date

arsal_90 — Thu, 27 Mar 2014 17:07:09 GMT

Hi Fernando

Please find the attached file. If this is not your desired result then let me know

Re: Re: Re: Join Max Date

arsal_90 — Thu, 27 Mar 2014 17:10:08 GMT

Stock:

LOAD ItemID,

PurchaseDate,

ItemCost,

'Purchase' as Flag

FROM

[Sales Data.xlsx]

(ooxml, embedded labels, table is Stock)

;

join

LOAD ItemID,

SalesDate,

SalesPrice,

'Sales' as Flag

FROM

[Sales Data.xlsx]

(ooxml, embedded labels, table is Sales);

Store Stock into SalesCostTable.qvd(qvd);

SalesCostTable:

LOAD ItemID,

PurchaseDate,

ItemCost,

SalesDate,

SalesPrice,

Flag

FROM

SalesCostTable.qvd

(qvd) where PurchaseDate <= SalesDate;

Re: Join Max Date

Thu, 27 Mar 2014 17:19:33 GMT

Hi Muhammad!!

As you do, the results are shown separately.

My intention is to make a 'max' on PurchaseDate, bringing the record immediately previous or equal to SaleDate.

The result should be right as the image.

Re: Re: Join Max Date

fernando_tonial — Thu, 27 Mar 2014 18:44:10 GMT

Olá Fernando!

Consegui utilizando o IntervalMatch.

Segue Script e anexo.

Purchase_TMP:
LOAD 
    ItemID,  
    ItemCost,
    PurchaseDate
FROM
tables.xlsx
(ooxml, embedded labels, table is Stock_Table)
Where not IsNull (ItemID);

Purchase:
LOAD 
    ItemID,  
    ItemCost,
    PurchaseDate    As PurchaseDateStart,
    If(ItemID<>Peek(ItemID),Date(Today()),Date(Peek(PurchaseDateStart)-1)) as PurchaseDateEnd
Resident Purchase_TMP 
Order by ItemID asc, PurchaseDate desc;

DROP Table Purchase_TMP;


MinMaxDate:
Load Min(PurchaseDateStart) as MinDate, Max(PurchaseDateEnd) as MaxDate resident Purchase;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;


Date:
Load Date(recno()+$(vMinDate)) as SaleDate Autogenerate vMaxDate - vMinDate;

DROP Table MinMaxDate;
Join (Purchase)
IntervalMatch (SaleDate) LOAD PurchaseDateStart, PurchaseDateEnd  Resident Purchase;


Inner Join (Purchase)
LOAD ItemID, 
     SaleDate, 
     SalePrice
FROM
tables.xlsx
(ooxml, embedded labels, table is Sale_Table);

DROP Table Date;

// Only for check values
QUALIFY *;
Stock:
LOAD 
    ItemID,  
    ItemCost,
    PurchaseDate
FROM
tables.xlsx
(ooxml, embedded labels, table is Stock_Table);

Sale:
LOAD ItemID, 
     SaleDate, 
     SalePrice
FROM
tables.xlsx
(ooxml, embedded labels, table is Sale_Table);