Date issue

[upaliwije]

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

[rubenmarin1]

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);

[upaliwije]

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

[rubenmarin1]

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;