Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Date issue

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

Screenshot_3.png

Pls help me to corect this issue

3 Replies
rubenmarin

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
Creator II
Creator II
Author

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

Screenshot_2.png

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

Screenshot_1.png

rubenmarin

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;