Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Digital Support
Digital Support

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.