Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
cosmicyes
Contributor III
Contributor III

Combine two tables via a canonical date

Dear all,

I thought I am on a good way reading this post:
https://community.qlik.com/t5/New-to-QlikView/Combining-two-date-fields-in-to-one-field-from-same-ta...

But my Solution still does not work.

I have two different tables from two different qvd - two kinds of complaints to our suppliers.
I want to combine both in a stacked bar chart so that I see the values per month added.

I have tried to get a canonical date to use as a dimension in the bar chart.

If I use the so created year and month in a cyclic dimenstion leads just to flat lines.
Also if I use the date, month or year in a listbox nothings happens if I select a value.

My (simplified) code so far:

**

SUB subFremdRekla

BeschichtungFremdRekla:

LOAD // Beschichtung:
BeschDatum,
BeschBANummer as BANummer

FROM [$(i_QVDDIR)\BeschichtungFremdRekla.qvd] (qvd);


MaterialFremdRekla:

LOAD // Material:
MatBANummer as BANummer,
MatGUDatum

FROM [$(i_QVDDIR)\MaterialFremdRekla.qvd] (qvd);

FremdReklaDateBridge:
LOAD
BeschichtungFremdRekla.BANummer,
BeschichtungFremdRekla.BeschDatum as FremdReklaCanonicalDate,
BeschichtungFremdRekla.BeschDatum as Type
Resident BeschichtungFremdRekla;

LOAD
MaterialFremdRekla.BANummer,
MaterialFremdRekla.MatGUDatum as FremdReklaCanonicalDate,
MaterialFremdRekla.MatGUDatum as Type
Resident MaterialFremdRekla;

FremdReklaDate: // get here the month and date
LOAD
date(FremdReklaDateBridge.FremdReklaCanonicalDate,'YYYY-MM-DD') as FremdReklaCanonicalDate,
year(Date(FremdReklaDateBridge.FremdReklaCanonicalDate,'YYYY-MM-DD')) as FremdReklaCanonicalJahr,
month(Date(FremdReklaDateBridge.FremdReklaCanonicalDate,'YYYY-MM-DD')) as FremdReklaCanonicalMonat
Resident FremdReklaDateBridge;

END SUB

**

Any ideas where my mistake is?
Or am I completely wrong?

Labels (2)
1 Reply
Brett_Bleess
Former Employee
Former Employee

Have a look at the following Design Blog post, believe that may get you what you need to get things working.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

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.