Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adrianfer
Contributor III
Contributor III

How to load this data

Hi everyone,

I am trying to figure out how to properly load this data. So I have these two sample tables to illustrate:

Sales (this is like a unit level table)

ColorMonthCountGroup
RedJan10G1
RedFeb20G2
BlueJan30G1

Target

ColorMonthTarget Count
RedJan50
RedFeb50
RedMar50
BlueJan50

Now, I want to display these in a pivot table as:

ColorMonthTarget (based on Color/Month)ActualSum of Target (based on Month)Actual
RedJan501010040
Feb50205020
Mar500500
BlueJan503010040

And If I do a filter on Group = G1, this is what should be displayed:

ColorMonthTarget (based on Color/Month)ActualSum of Target (based on Month)Actual
RedJan501010040
Feb500500
Mar500500
BlueJan503010040

Thanks in advance!

1 Solution

Accepted Solutions
Ivan_Bozov
Luminary
Luminary

If you really do not want synthetic table, try this:

Sales:

Load

     Color,

     Month,

     Count,

     Group

Inline

[

Color,Month,Count,Group

Red,Jan,10,G1

Red,Feb,20,G2

Blue,Jan,30,G1

]

(Delimiter is ',');

Target:

Outer Join Load

     Color,

     Month,

     [Target Count]

Inline

[

Color,Month,Target Count

Red,Jan,50

Red,Feb,50

Red,Mar,50

Blue,Jan,50

]

(Delimiter is ',');

Capture.PNG

vizmind.eu

View solution in original post

8 Replies
shraddha_g
Partner - Master III
Partner - Master III

in Load script,

link both tables using composite key of Color and Month fields.

For Ex.  Color &'-'& Month as Key.

This you will have to create in both tables (Sales & Target)

Ivan_Bozov
Luminary
Luminary

Hello! Load it like this, Qlik is going to automatically link the tables based on Color and Month:

Sales:

Load

     Color,

     Month,

     Count,

     Group

Inline

[

Color,Month,Count,Group

Red,Jan,10,G1

Red,Feb,20,G2

Blue,Jan,30,G1

]

(Delimiter is ',');

Target:

Load

     Color,

     Month,

     [Target Count]

Inline

[

Color,Month,Target Count

Red,Jan,50

Red,Feb,50

Red,Mar,50

Blue,Jan,50

]

(Delimiter is ',');

vizmind.eu
adrianfer
Contributor III
Contributor III
Author

Thanks, but doing that will create a synthetic key, which can be solved by making a composite key.

The problem is, the "Red-March" comination is not in  the Sales table, hence i cannot achieve this(emphasis on third row):

ColorMonthTarget (based on Color/Month)ActualSum of Target (based on Month)Actual
RedJan501010040
Feb50205020
Mar500500
BlueJan503010040
Ivan_Bozov
Luminary
Luminary

If you see a synthetic key in your data model and you did not expect it, then you most probably have a problem. If you know why it is there, you are good to go.


If you use the script that I posted you should be able to get the table in the format that you need it. Since there is no data for the combination Red-March, the Actual will show "-". You can avoid that by changing the expression, e.g. =If(Sum(Count)>0, Sum(Count), '0').

vizmind.eu
Ivan_Bozov
Luminary
Luminary

If you really do not want synthetic table, try this:

Sales:

Load

     Color,

     Month,

     Count,

     Group

Inline

[

Color,Month,Count,Group

Red,Jan,10,G1

Red,Feb,20,G2

Blue,Jan,30,G1

]

(Delimiter is ',');

Target:

Outer Join Load

     Color,

     Month,

     [Target Count]

Inline

[

Color,Month,Target Count

Red,Jan,50

Red,Feb,50

Red,Mar,50

Blue,Jan,50

]

(Delimiter is ',');

Capture.PNG

vizmind.eu
adrianfer
Contributor III
Contributor III
Author

But then if I do a filter on group, example "G1", it will only display the Month "Jan".

sumanta12
Creator II
Creator II

TARGET_COUNT: 

Load * Inline [ 

COLOR,MONTH,TARGET_COUNT 

Red,Jan,50 

Red,Feb,50 

Red,Mar,50 

Blue,Jan,50 

];

LEFT JOIN

SALES:

Load * Inline [ 

COLOR,MONTH,COUNT,GROUP 

Red,Jan,10,G1 

Red,Feb,20,G2 

Blue,Jan,30,G1 

];

LOAD

MONTH,

SUM(COUNT)        AS TOTAL_COUNT,

SUM(TARGET_COUNT) AS TOTAL_TARGET_COUNT

RESIDENT TARGET_COUNT

GROUP BY MONTH;

Ivan_Bozov
Luminary
Luminary

No problem, just add some set analysis, e.g.

Sum({<Group=>}[Target Count])

Capture2.PNG

vizmind.eu