topic Re: Date issue in QlikView

Date issue

upaliwije — Sat, 17 Feb 2018 11:14:42 GMT

Hi Friends

I have an issue like given in data model. I load data with cross table in folliwunf script

sales1:

CrossTable(TRN_DATE, BUDGET)

LOAD BCOD,

[28/2/2018],

[31/3/2018],

[30/4/2018],

[31/5/2018],

[30/6/2018],

[31/7/2018],

[31/8/2018],

[30/9/2018],

[31/10/2018],

[30/11/2018],

[31/12/2018]

FROM

D:\Qlikview\QVD\FACT_Table\MOTOR_CYCLE_BUDGET_2018.xls

(biff, embedded labels, header is 1 lines, table is Monthly$);

Store sales1 into D:\Qlikview\QVD\FACT_Table\MOTOR_CYCLE_BUDGET.qvd;

drop table sales1;

sales1:

LOAD BCOD,

Date(TRN_DATE)AS TRN_DATE,

BUDGET

FROM

D:\Qlikview\QVD\FACT_Table\MOTOR_CYCLE_BUDGET_2018.qvd

(qvd);

But when the data is loaded TRN_DATE field remail blank. as shown below

Pls help me to corect this issue

Re: Date issue

rubenmarin — Sat, 17 Feb 2018 11:41:23 GMT

Hi Upali, you can set an "Exit script;" before dropping sales1 to check the values loaded.

Maybe just needs to specify the input format for date:

LOAD BCOD,

Date(Date#(TR_DATE, 'DD/MM/YYYY')) AS TRN_DATE,

BUDGET

FROM

D:\Qlikview\QVD\FACT_Table\MOTOR_CYCLE_BUDGET_2018.qvd

(qvd);

Re: Date issue

upaliwije — Sat, 17 Feb 2018 11:59:26 GMT

Thanks Ruben As you I have done with exit script and the TRN_DATE is show like below

But once I cocatanate with another data set where TRN_DATE is in different format (given below) the TRN_DATE is not shown against Budget What Cold be the reason

Re: Date issue

rubenmarin — Sun, 18 Feb 2018 17:32:17 GMT

TRN_DATe is left aligned, meaning it's loaded as a string (not a number or a date), can be loaded as a date using:

Date(Date#(TR_DATE, 'DD/MM/YYYY')) AS TRN_DATE

You can do a resident load before the store:

sales2:

Noconcatenate

LOAD BCOD,

Date(Date#(TR_DATE, 'DD/MM/YYYY')) AS TRN_DATE,

BUDGET

Resident sales1;

Store sales2 into D:\Qlikview\QVD\FACT_Table\MOTOR_CYCLE_BUDGET.qvd;

drop table sales1;

drop table sales2;