Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
robbiewottie
New Contributor III

Granularity issue

Hi Guys,

I have an issue with granularity with dates en projectinformation. I have a table with projectheader information with monthly pipeline value snapshots and I have an table with Invoices related to that project. I don't always have an header date or an invoice date but what I need is one calendar to select dates in both tables.

I know this can be done with Date bridges and GenericKeys but can't figure it out...need some advice or sample script.

Thanks

Robbie

Dataset:

Projectheader

Project IDDateProject Pipeline Value
A1 January5000
A1 March5000
A1 December4000

Invoices

Project ID Invoice DateInvoice Value
A1 January1520
A23 January5000
A1 February4000
A15 February1500
A20 February50
A1 March10
A23 November6000
5 Replies
vinieme12
Esteemed Contributor II

Re: Granularity issue

You don't need a bridgetable here!!

these are two different types of info.


on the first table you have a Pipeline Date and the second table invoice date, why would join them using a ID& Date key?

Re: Granularity issue

Have you already looked at this and tried it?

Canonical Date

robbiewottie
New Contributor III

Re: Granularity issue

Hi Veneeth,

Can't join them,
I miss dates.If I want to know invoices of a project but i don't have a date in the header table (and that's possible because we created snapshots after a certain period) then the information is incomplete.

vinieme12
Esteemed Contributor II

Re: Granularity issue

if you want everything in the same table try below, remove the join keyword , it'll create a synthetic key but is safe in this scenario

LOAD [Project ID],

     [Invoice Date],

     DATE(MonthStart(DATE#([Invoice Date],'DD MMMM')),'DD MMMM') as Date,

     [Invoice Value]

FROM

[https://community.qlik.com/thread/247937]

(html, codepage is 1252, embedded labels, table is @2);

join

LOAD [Project ID],

     DATE#([Date],'DD MMMM') as Date,

     [Project Pipeline Value]

FROM

[https://community.qlik.com/thread/247937]

(html, codepage is 1252, embedded labels, table is @1);

rahulpawarb
Valued Contributor III

Re: Granularity issue

Hello Robbie,

Hope you are doing well!

You can add a Master Calendar with Two different Date Fields with same value as below (Keep the names same as Date fields from Invoices and Projectheader tables). This will join the master calendar table with other two tables.

//To be linked with Projectheader table's Date field

DateKey AS Date,


//To be linked with Invoices table's Invoice Date field

DateKey AS [Invoice Date]

This is draft version of solution; to further fine tune it you can implement the suggestion mentioned by stalwar1‌.

Regards!

Rahul