topic Re: Date Format Issue in QlikView

Date Format Issue

satishkurra — Sat, 13 Aug 2016 14:16:03 GMT

I'm using the below script to load the date in M/D/YYYY format.

My source is in a different format (YYYYMMDD)

After reloading i'm able to get the Sales as $100, But the date format is still YYYYMMDD.

Can someone help me to resolve this.

Am i missing something here?

Temp:

LOAD

Date(0, 'M/D/YYYY') as Date,

Num(0, '$#,##0') as Sales

AutoGenerate 0;

Sales:

LOAD * Inline [

ID,Date,Sales

1,20151201,100 ];

DROP Table Temp;

Re: Date Format Issue

kaushiknsolanki — Sat, 13 Aug 2016 14:25:16 GMT

Hi,

Make sure that the Date is not in Text format.

How will you check.

Just insert the Date listbox and if value are on Left side, it is a text.

If this is a case then

Try this Script.

Load Date(Date#(DATE,'YYYYMMDD'),'MM/DD/YYYY') as Date

From xyz;

Regards,

Kaushik Solanki

Re: Date Format Issue

satishkurra — Sat, 13 Aug 2016 14:58:47 GMT

This is not working...

I'm aware that we can convert directly in Date field as you mentioned above.

When i perform in the below way, it is not working...

TempFormatTable:

LOAD

Date(Date#(0,'YYYYMMDD'),'M/D/YYYY') as Date,

Num(0, '$#,##0') as Sales

AutoGenerate 0;

Sales:

LOAD * Inline [

ID,Date,Sales

1,20151201,100 ];

DROP Table TempFormatTable;

Re: Date Format Issue

Anonymous — Sat, 13 Aug 2016 15:50:20 GMT

Please try:

Date(Floor(Date#(your_date_field, 'YYYYMMDD')), 'MM/DD/YYYY') as your_Date ,

Re: Date Format Issue

kaushiknsolanki — Sat, 13 Aug 2016 16:02:19 GMT

Hi,

If you are trying the above script, it wont work. Because you are using formula at the table where you are not generating any data. and then you have inline table which generates the data but formula is missing.

Try below script.

Sales:

LOAD ID,Date(Date#(Date,'YYYYMMDD'),'M/D/YYYY') as Date,Sales Inline [

ID,Date,Sales

1,20151201,100 ];

Regards,

Kaushik Solanki

Re: Date Format Issue

satishkurra — Sat, 13 Aug 2016 16:39:24 GMT

Not sure if i'm trying to convey you guys in the right way.

AutoGenerate generates 0 as per the 0 in LOAD Statement.

If this is not correct, Sales is coming correctly.

I'm loading 100 and after reload it displays $100

Thanks

Satish

Re: Date Format Issue

pk2019 — Sat, 13 Aug 2016 17:50:31 GMT

Hi Satish,

Try this and problem solved.

Temp:

LOAD

Date(1,'YYYYMMDD') as NewDate,

Num(1, '$#,##0') as Sales

AutoGenerate 1;

Sales:

LOAD * Inline [

ID,NewDate,Sales

1,20151201,100 ];

DROP Table Temp;

NewSales:

Load

ID,

Date(NewDate,'M/D/YYYY') As MyDate,

Sales

Resident Sales;

Drop Table Sales;

Re: Date Format Issue

maxgro — Sat, 13 Aug 2016 18:13:57 GMT

The statement

Temp:

LOAD

Date(0, 'M/D/YYYY') as Date,

Num(0, '$#,##0') as Sales

AutoGenerate 0;

maybe is useful to set a default format for the field Date

but I don't think you can use it to interpret the values; remove the load ... autogenerate 0

I also think you already get the answer in the post of kaushik.solanki‌

Re: Date Format Issue

satishkurra — Sat, 13 Aug 2016 18:14:14 GMT

Thanks for this.

My concern here is we are performing transformation.

I should be achieving this without any transformation on source data...

Thanks

Satish

Re: Date Format Issue

satishkurra — Sat, 13 Aug 2016 18:15:38 GMT

Thanks for this.

Refer this.

Touchless Formatting | Qlikview Cookbook

Re: Date Format Issue

satishkurra — Sat, 13 Aug 2016 22:54:36 GMT

Can someone respond to this?

Re: Date Format Issue

rwunderlich — Sat, 13 Aug 2016 23:29:18 GMT

maxgro‌ is correct. The technique you are referring to in my blog post is for assigning a display format to a field, not interpreting input. Those are two different things.

-Rob