Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

Concatenate Date Fields

Hi Folks - I'm working with an excel file that has a date field format that I'm having trouble with  so I thought I'd break the date field into separate components and then rebuild the date.

This is what the portion of my script looks like:

[Issue date ],
// Date([Issue date ], 'MM/DD/YYYY') as IssDt,
Left([Issue date ], 4) as IssueYr,
Mid([Issue date ], 5,2) as IssueMonth,
Right([Issue date ], 2) as IssueDay,
Date([IssueMonth] & [IssueDay] & [IssueYr], 'MM/DD/YYYY') as IssueDt,

I keep getting an error that says Field not found error, 'IssueMonth' not found.   What am I doing wrong?

I've tried everything from changing the format in Excel to changing formats in QV.  This seemed like the easier path based on everything else I tried but I'd like to understand what I'm doing wrong in the script.

Thanks!!  George

 

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Date(MakeDate(Left([Issue date ], 4), Mid([Issue date ], 5,2), Right([Issue date ], 2)) as IssueDt

View solution in original post

4 Replies
stevejoyce
Specialist II
Specialist II

In your IssueDt field, you can't reference fields that haven't been created yet (i.e. IssueMonth & IssueDay).

You can do this with a preceeding load, or repeat the same logic, but when you are creating IssueDt, these fields don't exist yet:

 

preceding load example:

 

table:

load

*

,Date([IssueMonth] & [IssueDay] & [IssueYr], 'MM/DD/YYYY') as IssueDt

;

load

[Issue date ],
left([Issue date ], 4) as IssueYr,
Mid([Issue date ], 5,2) as IssueMonth,
Right([Issue date ], 2) as IssueDay
from <table>

sunny_talwar

Try this

Date(MakeDate(Left([Issue date ], 4), Mid([Issue date ], 5,2), Right([Issue date ], 2)) as IssueDt
gfisch13
Creator II
Creator II
Author

Thank you both for your replies.  I really wanted to stay away from the preceding load as I'm not too familiar with the procedure.  The Date function seemed like the right path i just didn't get the syntax correct.

stevejoyce
Specialist II
Specialist II

single load works if you only needed date.  if you wanted all the partial fields (year, month, day) AND the date field, you'd only have to define your partial fields 1x, and then use those definitions in your date field -> centralize logic. 

Or vice versa to what i originally posted, first create your date field, then your preceeding load can reference that date field and create partial fields in a single load pass.

Maybe not big here, but something to keep in mind in future.