topic Re: Subfield Command in QlikView

Subfield Command

Thu, 02 Oct 2014 20:00:51 GMT

My data in my table appears like:

EventID EventDate

1 9/1/2014 (carriage return)

9/2/2014 (carriage return)

9/3/2014 (carriage return)

What I want to end up with is:

Event ID EventDate

1 9/1/2014

1 9/2/2014

1 9/3/2014

Dropping the carriage return and duplicated the EventID. I would like to do this in the load statement because for each EventID there could be a different number of dates.

I have been trying the Subfield function and cant get it to work. Any help would be great.

Thanks,

David

Re: Subfield Command

JonnyPoole — Thu, 02 Oct 2014 20:12:25 GMT

It all depends if they are carriage returns, line feeds or both: (IE: chr(13) , chr(10) or both)

one or the other:

subfield([EventDate],chr(13))

subfield([EventDate],chr(10))

if both use replace with subfield:

subfield( replace(replace([EventDate],chr(10),''),chr(13),',') ,',')

Re: Subfield Command

Thu, 02 Oct 2014 20:30:10 GMT

So it would look like:

Load EventID,

subfield(EventDate,chr(13)) as EventDate

From......

Re: Subfield Command

mark_casselman — Thu, 02 Oct 2014 21:21:24 GMT

why not use the purechar function ?

Load EventID,

PurgeChar(EventDate, chr(10) & chr(13)) AS EventDate

Resident...

Re: Subfield Command

Thu, 02 Oct 2014 23:04:45 GMT

What I am looking the output to be is:

Event ID EventDate

1 9/1/2014

1 9/2/2014

1 9/3/2014

That is why I thought the subfield function was the answer but I have tried it and can get it to work. I am attaching the Excel File.

Re: Subfield Command

JonnyPoole — Thu, 02 Oct 2014 23:41:55 GMT

looks like chr(10) is the winner.

You need to use it in the load script. Here is the working code.

LOAD FSScheduleID,

EventDate,

SubField( EventDate , chr(10)) as Date

FROM

(biff, embedded labels, table is Sheet1$);