Combine Tables by ID - Match Year Month

[JacobLag]

Hello everybody,

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

Customer Group	Earning Actual	Year	Month
A	10.000 EUR	2019	1
B	12.000 EUR	2019	1
...	...	...	...

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

Customer Group	Earning Plan	Year Plan	Month Plan
A	11.000 EUR	2019	1
B	12.000 EUR	2019	1
...	...	...	...

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

[Tim_Wensink]

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 · Accepted solution]

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]

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

[JacobLag]

Thank you very much 🙂  it worked