topic Re: composite key in QlikView

composite key

Anonymous — Wed, 16 Nov 2016 08:24:39 GMT

Please find the attached dashboard.

Where i am creating a composite key and need to get the year and month field selected simaltnaeously in the application.

I want year & month as filters.

I need 2012 jan to be filtered in the list box.

Please let me know

Re: composite key

Anonymous — Wed, 16 Nov 2016 08:28:16 GMT

you donot have data from 2012

I would recommend to use a master calender

Re: composite key

sushil353 — Wed, 16 Nov 2016 08:30:51 GMT

Hi,

It is better if you create master calendar in your application.

Re: composite key

Anonymous — Wed, 16 Nov 2016 08:41:42 GMT

Thanks for the reply

Could you please provide me sample code for master calendar?

Re: composite key

its_anandrjs — Wed, 16 Nov 2016 08:46:58 GMT

Try this master calendar script code

DateData:

LOAD

YearStart( Today() ) + IterNo() as Date,

Rand() as Value

AutoGenerate 1

While (YearStart(Today()) + IterNo()) <= YearEnd(Today());

Final:

Load

Date(Date) as DateNew

Resident DateData;

Master Calendar Generation Script

https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

Regards

Anand

Re: composite key

rubenmarin — Wed, 16 Nov 2016 08:47:59 GMT

Hi John, check this document: Master Calendar Generation Script

In this part of the script:

Load

Min(InvoiceDate) AS MinDate,

Max(InvoiceDate) AS MaxDate

RESIDENT Invoice;

MinDate should store the start date of the calendar and MaxDate the end date, it can be dynamic absed on data like in the sample, you can fixed the dates using Date('YourDate') or you can create a variable that sets the min and max dates and use the variables to assign values to MinDate and MaxDate.

Re: composite key

Anonymous — Wed, 16 Nov 2016 09:13:59 GMT

Thanks everyone

However in my application i have only year and month i dont have any date field.

Please respond now

Re: composite key

rubenmarin — Wed, 16 Nov 2016 09:23:14 GMT

Hi John, you can create only one record per month changing from:

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

to:

Load Date(AddMonths(MinDate, IterNo() -1)) AS InvoiceDate While Date(AddMonths(MinDate, IterNo() -1)) <= Num(MaxDate);

Anyway, dont' worry too much for creating all dates, as it is another table you can create all dates without affecting your data. and if you load 5-6 years of dates there will be less than 2k extra records.

Re: composite key

its_anandrjs — Wed, 16 Nov 2016 09:38:45 GMT

I suggest some of the main points

1. Make Master Calendar

2. To connect with the Date field create the date field with Makedate function.

Ex:-

Load

Makedate(Year, Month) as Datefield

From Location;

Regards

Anand