Date format Issue

[qlikviewwizard]

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

[avinashelite]

can you please share the updated file with sample data

[qlikviewwizard]

Hi dreamer4,

Hi Avinashelite

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

[PrashantSangle]

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

[qlikviewwizard]

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.

[PrashantSangle · Accepted solution]

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

[nagarjuna_kotha]

Try This :

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

[sasiparupudi1]

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