MasterDateTMP

[#1]

Hi,

I am trying to create a master date from four different data sources and I am getting an error when trying to reload the model. Below is the script I used

MasterDateTMP:

load Date_EVENTS as MDate Resident Events;

load Date_JOURNAL as MDate  resident Journals;

load Date_OTILL as MDate  resident OnlineTill;

load Date_KIOSK as MDate  resident Kiosk_Payments;

exit script;

MasterDateTMP2:

NoConcatenate

load Distinct MDate Resident MasterDateTMP;

drop table MasterDateTMP;

MasterDate:

NoConcatenate

load MDate, MDate as Date_EVENTS, MDate as Date_JOURNAL, MDate as Date_OTILL, MDate as Date_KIOSK Resident MasterDateTMP2;

drop table MasterDateTMP2;

Can anyone assist in resolving this issue

[swuehl]

What error do you get?

Your script should exit after

exit script;

So you should end up having a MasterDateTMP table with one field, MDate (the Loads look ok, but since you haven't posted the error description and your full script, it's hard to help you here) in addition to the resident tables you used.

[#3]

Below is my full script. All four data sources have different date formats. I added exit script and the MDate is now reflecting but there is one problem. There are different date format showing when selecting MDate. See attached and advise

AGENCY_MASTER:

LOAD [Loc number] as LOC_MASTER,

     [Loc number] as LOC_EVENTS,

     [Loc number] as LOC_JOURNAL,

     [Loc number] as LOC_OTILL,

     [Agency Name],

     Area,

     TRIM([Agency Name]) & '_' & TRIM(Area) as KIOSKLINK,

     Region

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//STORE AGENCY_MASTER INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\Agency_Master.qvd (QVD);

Events:

LOAD //[Customer Nr],

     Date#(Date,'YYYY-MM-DD') as Date_EVENTS,

     Time as Event_Time,

     [Event Nr],

     [Event Name],

     Product,

     [Device Nr],

     [Reason Description],

     Username,

     [Full Name],

     Location as [Event Location],

     Name,

     Cell,

     Email,

     Region as Events_Region,

     [Location No] as LOC_EVENTS

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//STORE Events INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\Events.qvd (QVD);

Journals:

LOAD //[Customer Number] as [Customer Nr],

     [Journal Amount],

     if ([Journal Amount] >0,1,0) as DebitFlag,

     if ([Journal Amount] <0,1,0) as CreditFlag,    

     SMS,

     [Journal Type],

     [User Name],

     [User Full Name],

     [Location Number] as LOC_JOURNAL,

     [Date Created],

     Date#([Date Created],'YYYY-MM-DD') as Date_JOURNAL,

     Action,

     [Date Approved],

     Date(Date#([Date Approved],'m/d/yyyy h:mm'),'m/d/yyyy') as Approval_Date,

     [Journals Status],

     [First Name],

     Surname,

     [Account Number],

     [User Location] as [Journal Location],

     [Action Location],

     [Customer Type],

     [Account Type]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//STORE Journals INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\Journals.qvd (QVD);

OnlineTill:

LOAD //Date(Date#(Shift_Date,'m/d/yyyy h:mm'),'m/d/yyyy') as Date,

     Date#(Shift_Date,'YYYY-MM-DD') as Date_OTILL,

     User_Details,

     Shift_ID,

     //User_Home_Depot,

     LEFT(User_Home_Depot,8) as LOC_OTILL,

     BD_Number,

     Transaction_Count,

     Credit_Card,

     Cash,

     Cheque,

     Total_Amount

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

//STORE OnlineTill INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\OnlineTill.qvd (QVD);

KIOSK_MASTER:

LOAD Kiosk_Name,

     Kiosk_Location,

     Area as K_Area,

     Region as K_Region,

     TRIM(Kiosk_Location) & '_' & TRIM(Area) as KIOSKLINK

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//STORE KIOSK_MASTER INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\KIOSK_Master.qvd (QVD);

Kiosk_Payments:

LOAD TxnID,

     DateTime,

     Amount,

     CardClass,

     CardHolderName,

     TransactionStatusCode,

     TerminalID,

     MerchantNumber,

     AccountType,

     PrimaryAccountNumber,

     ResponseCode,

     ResponseCodeText,

     Status,

     StatusReason,

     accountNumber,

     customerNumber as [Customer Nr],

     date(Date,'YYYY-MM-DD') as Date_KIOSK,

     Time as Kiosk_Time,

     [Receipt Number],

     Name AS Kiosk_Name,

     Location as K_Location,

     Region as Kiosk_Region,

     RetrievalReferenceNumber

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//STORE Kiosk_Payments INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\Kiosk_Payments.qvd (QVD);

MasterDateTMP:

load Date_EVENTS as MDate Resident Events;

load Date_JOURNAL as MDate  resident Journals;

load Date_OTILL as MDate  resident OnlineTill;

load Date_KIOSK as MDate  resident Kiosk_Payments;

exit script;

MasterDateTMP2:

NoConcatenate

load Distinct MDate Resident MasterDateTMP;

drop table MasterDateTMP;

MasterDate:

NoConcatenate

load MDate, MDate as Date_EVENTS, MDate as Date_JOURNAL, MDate as Date_OTILL, MDate as Date_KIOSK Resident MasterDateTMP2;

drop table MasterDateTMP2;

exit script;

[PrashantSangle · Accepted solution]

Hi,

made this changes in your table

Date(Date#(Date,'YYYY-MM-DD'),'YYYY-MM-DD') as Date_EVENTS,//Events: Table

Date(Date#([Date Approved],'m/d/yyyy h:mm'),'YYYY-MM-DD') as Approval_Date,//Journals:Table

Date(Date#(Shift_Date,'YYYY-MM-DD'),'YYYY-MM-DD') as Date_OTILL, //OnlineTill: Table

date(Date,'YYYY-MM-DD') as Date_KIOSK, //Kiosk_Payments: Table

[swuehl]

It looks like QV does not correctly read in the dates / timestamps from your source tables.

Please check

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Check for every single source table what the exact format of the date / timestamp field is, and that QV interpretes this format correctly (by using a default format or the interpretation functions Date#() / TimeStamp#() with an appropriate format code).

Consider using floor() or daystart() to convert a TimeStamp value into a real Date value (stripping off the time part).

Consider formatting your values using Date() / TimeStamp() so you get a consistent text representation.

edit:

The format code for

...

  Date(Date#([Date Approved],'m/d/yyyy h:mm'),'m/d/yyyy') as Approval_Date,

...

seems wrong, Month and Date Format codes are in capital letters:

  Date(Date#([Date Approved],'M/D/YYYY h:mm'),'M/D/YYYY') as Approval_Date,

'm' denotes minutes.

[SunilChauhan]

Try to make all date in same format.

use below code inplace

MasterDateTMP:

load Date(Date_EVENTS,'YYYY-MM-DD hh:mm:ss' )as MDate Resident Events;

load Date(Date_JOURNAL,'YYYY-MM-DD hh:mm:ss' ) as MDate  resident Journals;

load Date(Date_OTILL,'YYYY-MM-DD hh:mm:ss' ) as MDate  resident OnlineTill;

load Date(Date_KIOSK,'YYYY-MM-DD hh:mm:ss' ) as MDate  resident Kiosk_Payments;

hope this helps

[#7]

Thanks to you all for your input I just applied the script below and the date field is working fine.

Date(Date#(Date,'YYYY-MM-DD'),'YYYY-MM-DD') as Date_EVENTS,//Events: Table

Date(Date#([Date Approved],'m/d/yyyy h:mm'),'YYYY-MM-DD') as Approval_Date,//Journals:Table

Date(Date#(Shift_Date,'YYYY-MM-DD'),'YYYY-MM-DD') as Date_OTILL, //OnlineTill: Table

date(Date,'YYYY-MM-DD') as Date_KIOSK, //Kiosk_Payments: Table

[maxgro]

- first I'll would change

date(Date,'YYYY-MM-DD') as Date_KIOSK,

to 

date#(Date,'YYYY-MM-DD') as Date_KIOSK,

- second

if you add a field to MasterDateTMP you can  find the table with date problem

MasterDateTMP:

load Date_EVENTS as MDate, 'E' as Type Resident Events;

load Date_JOURNAL, 'J' as Type as MDate  resident Journals;

load Date_OTILL as MDate, 'O' as Type   resident OnlineTill;

load Date_KIOSK as MDate, 'K' as Type   resident Kiosk_Payments;

good doc about date

Primer for QlikView Date fields

QlikView Date fields