Date Format Issue

[satishkurra]

Hi

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;

[kaushiknsolanki]

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

[satishkurra]

Hi

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;

[Anonymous]

Please try:

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

[kaushiknsolanki]

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

[satishkurra]

Hi

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

[pk2019]

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;

[maxgro]

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‌

[satishkurra]

Hi

Thanks for this.

My concern here is we are performing transformation.

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

Thanks

Satish

[satishkurra · Accepted solution]

Thanks for this.

Refer this.

Touchless Formatting | Qlikview Cookbook