Combining 3 tables data into 1

[qlikviewwizard]

Hi All,

I have 3 tables. Sales, Forecast_Sales,Actual_Sales.

Forecast_Sales,Actual_Sales data is in crosstab table on YearMonth Level.

Sales data is in normal table and data is in Date Level.

I want to join/concatenate into one table to resolve the data model issue.

Could you please advise me in combining these 3 into 1.

Thanks in advance.

[sunny_talwar]

What is this data on the actual tab without headers?

And secondly I know you mentioned that you want to join/concatenate the data, but do you have an idea of how you want to see the combined table to look like (expected output)?

[qlikviewwizard]

Hi Sunny,

Please ignore the unrelated data.

I want to see the data as below:

[agni_gold]

Hi Friend ,

I have modified your code , you can optimize more , i have done quickly .

Your Current data model is like

I think you want this ?

Attached your file.

Thanks

[sunny_talwar · Accepted solution]

Not sure if this is what you want:

Script:

Table1:

LOAD Country,

    Product,

    Date,

    Sales

FROM

[Data - Copy.xlsx]

(ooxml, embedded labels, table is Sales);

Table2:

CrossTable(YearMonth, ActualSalesData, 2)

LOAD Country,

    Product,

    [2015-01],

    [2015-02],

    [2015-03],

    [2015-04],

    [2015-05],

    [2015-06],

    [2015-07],

    [2015-08],

    [2015-09],

    [2015-10],

    [2015-11],

    [2015-12]

FROM

[Data - Copy.xlsx]

(ooxml, embedded labels, table is Actual);

Table3:

CrossTable(YearMonth, ForecastSalesData, 2)

LOAD Country,

    Product,

    [2015-01],

    [2015-02],

    [2015-03],

    [2015-04],

    [2015-05],

    [2015-06],

    [2015-07],

    [2015-08],

    [2015-09],

    [2015-10],

    [2015-11],

    [2015-12]

FROM

[Data - Copy.xlsx]

(ooxml, embedded labels, table is Forecast);

FinalTable:

LOAD MonthName(Date#(YearMonth, 'YYYY-MM')) as MonthYear,

  ActualSalesData,

  Country,

    Product

Resident Table2;

Join(FinalTable)

LOAD MonthName(Date#(YearMonth, 'YYYY-MM')) as MonthYear,

  ForecastSalesData,

  Country,

    Product

Resident Table3;

Join(FinalTable)

LOAD Country,

    Product,

    MonthName(Date) as MonthYear,

    Date,

    Sales

FROM

[Data - Copy.xlsx]

(ooxml, embedded labels, table is Sales);

DROP Tables Table1, Table2, Table3;

Data in Table Box:

Chart you were looking for:

Attaching the QlikView document for reference.

I hope this will help.

Best,

Sunny