topic Re: Granularity issue in QlikView

Granularity issue

Anonymous — Mon, 30 Jan 2017 09:54:26 GMT

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 ID	Date	Project Pipeline Value
A	1 January	5000
A	1 March	5000
A	1 December	4000

Invoices

Project ID	Invoice Date	Invoice Value
A	1 January	1520
A	23 January	5000
A	1 February	4000
A	15 February	1500
A	20 February	50
A	1 March	10
A	23 November	6000

Re: Granularity issue

vinieme12 — Mon, 30 Jan 2017 10:18:03 GMT

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

sunny_talwar — Mon, 30 Jan 2017 10:26:30 GMT

Have you already looked at this and tried it?

Canonical Date

Re: Granularity issue

Anonymous — Mon, 30 Jan 2017 10:26:32 GMT

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.

Re: Granularity issue

vinieme12 — Mon, 30 Jan 2017 10:29:20 GMT

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

Re: Granularity issue

rahulpawarb — Mon, 30 Jan 2017 12:18:32 GMT

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