Creating a Month, Year and Day on Calender

Mon, 17 Mar 2014 08:20:19 GMT

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;

Re: Creating a Month, Year and Day on Calender

datanibbler — Mon, 17 Mar 2014 08:34:07 GMT

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

Re: Creating a Month, Year and Day on Calender

jagannalla — Mon, 17 Mar 2014 08:37:42 GMT

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

topic Creating a Month, Year and Day on Calender in QlikView

Creating a Month, Year and Day on Calender

Re: Creating a Month, Year and Day on Calender

Re: Creating a Month, Year and Day on Calender