topic Re: Combine Tables by ID - Match Year Month in QlikView

Combine Tables by ID - Match Year Month

JacobLag — Sat, 16 Nov 2024 19:46:41 GMT

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

Re: Combine Tables by ID - Match Year Month

Tim_Wensink — Thu, 31 Oct 2019 15:42:03 GMT

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;

Re: Combine Tables by ID - Match Year Month

JGMDataAnalysis — Thu, 31 Oct 2019 15:43:32 GMT

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

Re: Combine Tables by ID - Match Year Month

Brett_Bleess — Thu, 07 Nov 2019 19:27:55 GMT

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

Re: Combine Tables by ID - Match Year Month

JacobLag — Fri, 08 Nov 2019 07:59:46 GMT

Thank you very much 🙂 it worked