topic Re: MasterDateTMP in QlikView

MasterDateTMP

Sat, 15 Mar 2014 13:35:34 GMT

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

Re: MasterDateTMP

swuehl — Sat, 15 Mar 2014 13:52:03 GMT

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.

Re: MasterDateTMP

Sat, 15 Mar 2014 14:59:13 GMT

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;

Re: MasterDateTMP

PrashantSangle — Sat, 15 Mar 2014 15:22:19 GMT

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

Re: MasterDateTMP

swuehl — Sat, 15 Mar 2014 15:23:27 GMT

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.

Re: MasterDateTMP

SunilChauhan — Sat, 15 Mar 2014 15:27:10 GMT

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

Re: MasterDateTMP

Sat, 15 Mar 2014 15:30:30 GMT

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

Re: MasterDateTMP

maxgro — Sat, 15 Mar 2014 15:30:42 GMT

- first I'll would change

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

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