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: 
JacobLag
Contributor
Contributor

Combine Tables by ID - Match Year Month

Hello everybody,

I have two tables (Customer, Plan). The Customer Table is automatically imported into QV from our System, for example:

Customer GroupEarning ActualYearMonth
A10.000 EUR20191
B12.000 EUR20191
............

 

The Plan Table is created by Excel and manually imported into QV, for example:

Customer GroupEarning PlanYear PlanMonth Plan
A11.000 EUR20191
B12.000 EUR20191
............

 

I want to create a Graph with Month as Dimension (Year and Customer Group as Filter Options), showing me the Plan and Actual Earnings in curves.

So, I combined the tables by the field Customer Group. But how can I determine a common Time Dimension for the Graph?

Do you have any Ideas 🙂 ?

Labels (3)
1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

Script:

FinalTable:
NOCONCATENATE
LOAD
	'Customer' AS Type,
    "Customer Group",
    "Earning Actual",
     Year,
     Month
FROM YourSystem
;
CONCATENATE(FinalTable)
LOAD
	'Plan' AS Type,
    "Customer Group",
    "Earning Plan",
    "Year Plan" AS Year,
    "Month Plan" AS Month
FROM YourExcelFile;

 

Measures:

Sum([Earning Actual])
Sum([Earning Plan])

View solution in original post

4 Replies
Tim_Wensink
Partner - Contributor III
Partner - Contributor III

Hello Jacob,

I would combine the two tables into one. You then get one month field and one year field which you can use as dimensions. Each line then has an [Earning actual] and [Earning plan] for that month. You script would be something like:

 

Earnings:
load * inline
[
Customer Group, Earning Actual, Year, Month
A, 10000, 2019, 1
B, 12000, 2019, 1
];

Earning_Plans:
load * inline
[
Customer Group, Earning Plan, Year Plan, Month Plan,
A, 11000, 2019, 1
B, 12000, 2019, 1
];

left join (Earnings)

load
[Customer Group],
[Earning Plan],
[Year Plan] as Year,
[Month Plan] as Month
resident Earning_Plans;

drop table Earning_Plans;

JGMDataAnalysis
Creator III
Creator III

Script:

FinalTable:
NOCONCATENATE
LOAD
	'Customer' AS Type,
    "Customer Group",
    "Earning Actual",
     Year,
     Month
FROM YourSystem
;
CONCATENATE(FinalTable)
LOAD
	'Plan' AS Type,
    "Customer Group",
    "Earning Plan",
    "Year Plan" AS Year,
    "Month Plan" AS Month
FROM YourExcelFile;

 

Measures:

Sum([Earning Actual])
Sum([Earning Plan])
Brett_Bleess
Former Employee
Former Employee

Jacob, did either of the posts get you what you needed?  If so, do not forget to return to the thread and use the Accept as Solution button on the post(s) that worked for you.  This gives the poster(s) credit for the help and lets others that come across the thread confirmation that things worked.  If you are still working on things, leave an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
JacobLag
Contributor
Contributor
Author

Thank you very much 🙂  it worked