topic Re: Calender in QlikView

Calender

upaliwije — Wed, 25 Jun 2014 06:30:27 GMT

Vehicle:

LOAD FLAG,

BCOD,

date(FROM_DATE,'DD-MM-YYYY h:mm:ss')as FROM_DATE,

NAME,

Amount

PREMIUM,

Vehicle_No

FROM

[..\QVD\FACT_Table\Vehicle_Type.qvd]

(qvd);

I have loaded above QVD file and created a Pivot Table and the above script is linked to the following master calender

Calender:

LOAD

Day(FROM_DATE) AS DAY,

Date(FROM_DATE) AS FROM_DATE,

Week(FROM_DATE) AS WEEK,

Year(FROM_DATE) AS YEAR,

Month(FROM_DATE) As MONTH,

Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week;

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

Load

Min(FROM_DATE) AS MinDate,

Max(FROM_DATE) AS MaxDate

RESIDENT Vehicle;

In my pivot table when I select all records I get the correct Amounts. But If Select a Year , the figure shown in the pivot table is very much less than the actual figures corresponding to the particular year

Eg Actual No of vehicle for Year 2012 is 10000

But If Select the year from my Year_List Box the number become 125

How can This happen? Is it due to a error in my script ? Pls help me to solve this problem

Re: Calender

ashfaq_haseeb — Wed, 25 Jun 2014 06:36:03 GMT

Hi,

Look at the below post

http://community.qlik.com/docs/DOC-3858

Regards

ASHFAQ

Re: Calender

sujeetsingh — Wed, 25 Jun 2014 06:41:16 GMT

Load

Min(FROM_DATE) AS MinDate,

Max(FROM_DATE) AS MaxDate

RESIDENT Vehicle;

CalendarMaster:

LOAD

Date(FROM_DATE) AS FROM_DATE,

Year(FROM_DATE) AS Year,

'Q' & Ceil(Month(FROM_DATE) / 3) AS Quarter,

Month(FROM_DATE) As Month,

Day(FROM_DATE) As Day,

Week(FROM_DATE) As Week;

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

Re: Calender

jagan — Wed, 25 Jun 2014 06:47:08 GMT

Hi,

The key field FROM_DATE is having in different formats one with timestamp and another without timestamp.

Vehicle:

LOAD FLAG,

BCOD,

date(Floor(FROM_DATE),'DD-MM-YYYY')as FROM_DATE,

NAME,

Amount

PREMIUM,

Vehicle_No

FROM

[..\QVD\FACT_Table\Vehicle_Type.qvd]

(qvd);

Calender:

LOAD

Day(FROM_DATE) AS DAY,

Date(FROM_DATE, 'DD-MM-YYYY') AS FROM_DATE,

Week(FROM_DATE) AS WEEK,

Year(FROM_DATE) AS YEAR,

Month(FROM_DATE) As MONTH,

Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week;

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

Load

Floor(Min(FROM_DATE)) AS MinDate,

Floor(Max(FROM_DATE)) AS MaxDate

RESIDENT Vehicle;

Hope this helps you.

Regards,

jagan.

Re: Calender

Anonymous — Wed, 25 Jun 2014 06:48:33 GMT

Hi,

First of all, is it really necessary to create Calendar table for above scenario. If it is required can we just create based on FROM_DATE. For example below,

Calender:

LOAD

FROM_DATE,

Day(FROM_DATE) AS DAY,

Date(FROM_DATE) AS FROM_DATE,

Week(FROM_DATE) AS WEEK,

Year(FROM_DATE) AS YEAR,

Month(FROM_DATE) As MONTH,

Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week

Resident Vehicle;

If it is not require, you can create all these calendar fields inside Vehicle table. For example below,

Vehicle:

LOAD FLAG,

BCOD,

Date(FROM_DATE,'DD-MM-YYYY h:mm:ss')as FROM_DATE,

Day(FROM_DATE) AS DAY,

Date(FROM_DATE) AS FROM_DATE,

Week(FROM_DATE) AS WEEK,

Year(FROM_DATE) AS YEAR,

Month(FROM_DATE) As MONTH,

Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week,

NAME,

Amount

PREMIUM,

Vehicle_No

FROM

[..\QVD\FACT_Table\Vehicle_Type.qvd]

(qvd);

Thanks

Re: Calender

upaliwije — Wed, 25 Jun 2014 07:46:33 GMT

Thanks