Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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;
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
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.
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
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
- 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