Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have created the script below and I would like to create Year, Month and Day using MDate. Can you please advise how I need to do that?
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#(Date,'m/d/yyyy h:mm'),'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
//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,
Date(Date#([Date Created],'m/d/yyyy h:mm'),'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]
//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(Date#(Shift_Date,'m/d/yyyy h:mm'),'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
(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
//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
//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:
load MDate, MDate as Date_EVENTS, MDate as Date_JOURNAL, MDate as Date_OTILL, MDate as Date_KIOSK Resident MasterDateTMP2;
drop table MasterDateTMP2;
Hi Elijah,
where in that script is your MDate? 😉
It should be easy - provided your MDate is a proper date (rgd. the datatype)
=> There is a YEAR() function
=> There is a Month() function
=> You can get the weekday (Mon, Tue ...) using the WEEKDAY() function.
HTH
Best regards,
DataNibbler
Calendar:
LOAD
MDate,
Year(MDate) as Year,
Month(MDate) as Month,
Day(MDate) as Day
Resident MasterDate;
Thanks,
Jagan
