Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a Month, Year and Day on Calender

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;

2 Replies
datanibbler
Champion
Champion

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
Partner - Specialist III
Partner - Specialist III

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