Creating a Month, Year and Day on Calender

[#1]

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

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,

     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]

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(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

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;

[datanibbler]

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

[jagannalla]

Hi,

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;

Calendar:

LOAD

MDate,

Year(MDate) as Year,

Month(MDate) as Month,

Day(MDate) as Day

Resident MasterDate;

Thanks,

Jagan