Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Duplicate records being created when Month, Year etc are applied to a bad date

Hi,

I have a bad date format and am uisng the following code to extract the Date, Year, and Month.

Thanks to those who helped with thi sbefore.

However multiple records are being created when I try to extract the Date, Year and Month.

Can anyone suggest why this is occuring. I've attached a simple spreadsheet and QV app. to replicate the problem.

All help appreciated.

Tony

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Duplicate records being created when Month, Year etc are applied to a bad date

You are getting multiple records, because you are using subfield() function without stating the sub field to take, this will create a record per subfield found. You use subfield twice in your load, thus you get 2x2 records created per input record (having 2 sub fields in your input text).

If you want stick close to your original idea, just state the sub field to use with the third paramater to subfield():

date(date#(Trim(Subfield(mid( baddate,7),'/',1)), 'MMM YYYY') ,'DD/MMM/YYYY') as gooddate,

3 Replies
jason_michaelid
Honored Contributor II

Duplicate records being created when Month, Year etc are applied to a bad date

I can't open the qvw right now so can't see what you're using to extract the date.  Looking at the spreadsheet example I'd try:

Date(Date#(TRIM(TextBetween(baddate,' ','/')),'MMMM YYYY'))     AS     GoodDate

Hope this helps,

Jason

MVP
MVP

Duplicate records being created when Month, Year etc are applied to a bad date

You are getting multiple records, because you are using subfield() function without stating the sub field to take, this will create a record per subfield found. You use subfield twice in your load, thus you get 2x2 records created per input record (having 2 sub fields in your input text).

If you want stick close to your original idea, just state the sub field to use with the third paramater to subfield():

date(date#(Trim(Subfield(mid( baddate,7),'/',1)), 'MMM YYYY') ,'DD/MMM/YYYY') as gooddate,

Not applicable

Duplicate records being created when Month, Year etc are applied to a bad date

Thanks swuehl ,

That's great,

I really appreciate your help.

I'd pretty much given up on getting a solution.

I've posted the solution in case any else has the same problem.

Tony

Community Browser