Date issue

[guillaume_gorli]

Hi All,

Please see below the issue i am facing :

I have a test database with :

Date

Part Number

Cost

Then i have a second database showing a cost saving target per month :

What i am trying to do is to sum up in a chart a cost including the target savings per part number.

The output should look like :

Although what i got is :

Thanks for your help

Guillaume

[vinieme12]

try joining the data with a new field

LOAD Date,

     [Part Number],

     Cost,

    MonthEnd(Date) as SavingsDate

FROM

(ooxml, embedded labels, table is Sales);

/// SAVINGS ///

LOAD Date as SavingsDate,

     Savings

FROM

(ooxml, embedded labels, table is Savings);

[guillaume_gorli]

Hi Vineeth,

It does not seem to work this way :

[vhespinog]

Hi.

Sales:

LOAD Year(Date) & Month(Date) as %Date,

     Date,

     [Part Number],

     Cost

FROM

[..\Downloads\TEST QLIK.xlsx]

(ooxml, embedded labels, table is Sales);

Savings:

LOAD

     Year(Date) & Month(Date) as %Date,

     Date as SavingsDate,

     Savings

FROM

[..\Downloads\TEST QLIK.xlsx]

(ooxml, embedded labels, table is Savings);

[vinieme12 · Accepted solution]

sorry, slight change below

LOAD Date,

    [Part Number],

    Cost,

    MonthEnd(Date) as SavingsDate

FROM

(ooxml, embedded labels, table is Sales);

/// SAVINGS ///

LOAD MonthEnd(Date) as SavingsDate,

    Savings

FROM

(ooxml, embedded labels, table is Savings);

[vinieme12]

or you can directly use MonthName()

[anushree1]

Hi,

Please check if this at serves the requirement

[prieper]

Think that you should use INTERVALMATCH. Would suggest to enter a date with 0% Savings for 1/1/2017 (begin of the Sales-activities), but anyhow:

/// COST ///

Sales:

LOAD Date,

     [Part Number],

     Cost

FROM

(ooxml, embedded labels, table is Sales);

/// SAVINGS ///

Savings:

LOAD Date,

     Savings

FROM

(ooxml, embedded labels, table is Savings);

Savings_FmTo:

    LOAD

        Date            AS Date_Fm,

        PREVIOUS(Date)    AS Date_To,

        Savings

    RESIDENT

        Savings

    ORDER BY

        Date DESC;

DROP TABLE Savings;

INTERVALMATCH (Date) LOAD Date_Fm, Date_To RESIDENT Savings_FmTo;

HTH Pete

[guillaume_gorli]

Thanks a lot