topic Re: Date format Issue in QlikView

Date format Issue

qlikviewwizard — Tue, 22 Sep 2015 10:44:26 GMT

Hi All,

I am getting date format issue as described below:

INVDate is in YYYYMMDD format, data is getting from the database.

ActaualYearMonth,BudgetYearMonth format is in YYYY-MM and data is getting from Xls files.

Unable to get the data due to date format different.

Please help me.

Data:

YearMonth	Actual_YearMonth	Budget_YearMonth
2014-01	2015-01	2015-01
2014-01	2015-02	2015-02
2014-01	2015-03	2015-03
2014-01	2015-04	2015-04
2014-01	2015-05	2015-05
2014-01	2015-06	2015-06
2014-01	2015-07	2015-07
2014-01	2015-08	2015-08

Re: Date format Issue

Anonymous — Tue, 22 Sep 2015 10:53:17 GMT

you can convert INVDate format to ActaualYearMonth,BudgetYearMonth format using this

date(date#(INVDate,'YYYYMMDD'),'YYYY-MM')

Re: Date format Issue

Gabriel — Tue, 22 Sep 2015 11:07:24 GMT

Hi,

What I would suggest is to have a Calendar Dimension table. All your dates from other Dimension/Fact tables should associate with Dates,Month,Year, Year-Month in the Calendar dimension table

Re: Date format Issue

tripatirao — Tue, 22 Sep 2015 11:39:55 GMT

I think you can use makedate() function.

MakeDate(YYYY [ , MM [ , DD ] ] )

Returns a date calculated from the year YYYY, the month MM and the day DD.

If no month is stated, 1(January) is assumed.

If no day is stated, 1 (the 1:st) is assumed.

Examples:

makedate(1999) returns 1999-01-01

makedate(99) returns 0099-01-01

makedate(1992,12) returns 1992-12-01

makedate(1999,2,14) returns 1999-02-14

Re: Date format Issue

qlikviewwizard — Tue, 22 Sep 2015 12:11:23 GMT

Hi Chandrasekar,

It is not working.

Re: Date format Issue

Anonymous — Tue, 22 Sep 2015 12:25:52 GMT

Re: Date format Issue

qlikviewwizard — Wed, 23 Sep 2015 02:35:45 GMT

Hi All,

Henric Cronström ,Avinash R ,Sasidhar Parupudi,swuehl

Any please help me.

Re: Date format Issue

avinashelite — Wed, 23 Sep 2015 05:30:20 GMT

Your Sales table is not connected with other tables ......create a link for the same

Re: Date format Issue

qlikviewwizard — Wed, 23 Sep 2015 05:46:57 GMT

Hi Avinash,

Please check the below screens:

I joined between 3 tables with concatenated key country,product,yearmonth columns.

OP: %CntryProdMnthKey = country&product&OP_YearMonth

Forecast: %CntryProdMnthKey = country&product&Forecast_YearMonth

OP: %CntryProdMnthKey = country&product&OP_YearMonth

But associate betwwn these 3 columns are not happens.

OP_YearMonth and Forecast_YearMonth are coming from xls file.

YearMonth is derived from Sales QVD:

date(date#(MakeDate(SalesYear,if (SalesMonth<='9', '0'&SalesMonth,SalesMonth),'01'),'M/D/YYYY'),'YYYY-MM') as YearMonth,

Please advise.

Re: Date format Issue

PrashantSangle — Wed, 23 Sep 2015 05:52:30 GMT

Hi,

can you post screen shot of %CntryProdMnthKey field after above selection.

also create same key with different name in each table and also post there screen shot.

Regards

Re: Date format Issue

avinashelite — Wed, 23 Sep 2015 05:57:48 GMT

can you please share the updated file with sample data

Re: Date format Issue

qlikviewwizard — Wed, 23 Sep 2015 06:13:35 GMT

Hi dreamer4,

Hi Avinashelite

As the data is confidential unable to provide original qvw. I will upload soon.

Re: Date format Issue

PrashantSangle — Wed, 23 Sep 2015 06:22:00 GMT

Hi,

create new field in each table and post there screen shot.Three field like

In OP Table: %CntryProdMnthKey1 = country&product&OP_YearMonth

In Forecast Table : %CntryProdMnthKey2 = country&product&Forecast_YearMonth

In Sales Table : %CntryProdMnthKey3 = country&product&OP_YearMonth

Keep your existing key %CntryProdMnthKey1 dont remove it.

Regards

Re: Date format Issue

qlikviewwizard — Wed, 23 Sep 2015 06:51:32 GMT

Hi max dreamer,

I used the columns as below for Sales Table as there is no OP_YearMonth in that table.

In Sales Table : %CntryProdMnthKey3 = country&product&YearMonth

%CntryProdMnthKey3 showing different value for YearMonth

Please check the screen.

Re: Date format Issue

PrashantSangle — Wed, 23 Sep 2015 07:02:16 GMT

Hi,

that is your issue. there date field converted into num.

so you have to convert it either %CntryProdMnthKey1 and %CntryProdMnthKey2 or %CntryProdMnthKey3

modify your key

%CntryProdMnthKey3 = country&product&Date(YearMonth,'YYYY-MM')

Then reload it and check.

Regards

Re: Date format Issue

nagarjuna_kotha — Wed, 23 Sep 2015 07:03:58 GMT

Try This :

>>>>date(date#(INVDate,'YYYYMMDD'),'YYYY-MM')

Re: Date format Issue

sasiparupudi1 — Wed, 23 Sep 2015 07:55:50 GMT

modify the key values in the tables where the date format is internal value as below..

%CntryProdMnthKey = country&product&Date(YearMonth,'YYYY-MM')

hopefully this will make the link work between all the tables who have this common key

hth

Sasi