topic Re: Combining 3 tables data into 1 in QlikView

Combining 3 tables data into 1

qlikviewwizard — Sun, 20 Sep 2015 05:54:14 GMT

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.

Re: Combining 3 tables data into 1

sunny_talwar — Sun, 20 Sep 2015 07:48:30 GMT

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)?

Re: Combining 3 tables data into 1

qlikviewwizard — Sun, 20 Sep 2015 08:40:06 GMT

Hi Sunny,

Please ignore the unrelated data.

I want to see the data as below:

Re: Combining 3 tables data into 1

agni_gold — Sun, 20 Sep 2015 11:05:52 GMT

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

Re: Combining 3 tables data into 1

sunny_talwar — Sun, 20 Sep 2015 13:46:44 GMT

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