Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

Combining 3 tables data into 1

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.

1 Solution

Accepted Solutions
sunny_talwar

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:

Capture.PNG

Chart you were looking for:

Capture.PNG

Attaching the QlikView document for reference.

I hope this will help.

Best,

Sunny

View solution in original post

4 Replies
sunny_talwar

What is this data on the actual tab without headers?

Capture.PNG

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
Master II
Master II
Author

Hi Sunny,

Please ignore the unrelated data.

I want to see the data as below:

Capture.PNG

agni_gold
Specialist III
Specialist III

Hi Friend ,

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

Your Current data model is like

DataModel.jpg

I think you want this ?

Attached your file.

Thanks

sunny_talwar

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:

Capture.PNG

Chart you were looking for:

Capture.PNG

Attaching the QlikView document for reference.

I hope this will help.

Best,

Sunny

View solution in original post