topic Data parsing challenge in QlikView

Data parsing challenge

terrusie — Wed, 25 Nov 2020 16:16:04 GMT

Hello. I'm having trouble Loading data in because of its strange format, and I am not permitted to alter the data file. I imagine that I will need to use subfield somehow, but I'm at a loss as to how Qlikview can get it into the right form.

Here's the form of the data in the data file:

Identifier	Series
100	1 - Orange / 4 - Purple / 10 -Yellow
200	11 - Green / 60 - Orange
500	1 - Purple / 4 - Green / 10 - Yellow / 30 - Orange

I need to load the data into three related fields as shown below:

Identifier	Series Number	Series Type
100	1	Orange
100	4	Purple
100	10	Yellow
200	11	Green
200	60	Orange
500	1	Purple
500	4	Green
500	10	Yellow
500	30	Orange

Ideally, I would arrange the data file to be more friendly to the load, but I have no control over the file or its format (it is used for other purposes that depend on the format it's in). Any ideas on how to do this?

Re: Data parsing challenge

sunny_talwar — Wed, 02 May 2018 16:37:28 GMT

Try this

Table:

LOAD Identifier,

Trim(SubField(Series, '-', 1)) as [Series Number],

Trim(SubField(Series, '-', 2)) as [Series Type];

LOAD Identifier,

SubField(Series, '/') as Series;

LOAD * INLINE [

Identifier, Series

100, 1 - Orange / 4 - Purple / 10 -Yellow

200, 11 - Green / 60 - Orange

500, 1 - Purple / 4 - Green / 10 - Yellow / 30 - Orange

];

Re: Data parsing challenge

terrusie — Wed, 02 May 2018 17:06:01 GMT

Sunny,

Thanks. I have one other problem. Evidently the data have 2 "-" and only the first one matters:

Example:

100, 1-Orange-ST1 / 4-Purple-ST3 / 10-Yellow-ST6

Where the "Series Type" should be:

Orange-ST1

Purple-ST3

Yellow-ST6

Is there a way to say:

Subfield(Series,'-',Everything after the first)?

Re: Data parsing challenge

sunny_talwar — Wed, 02 May 2018 17:08:22 GMT

Try this

Table:

LOAD Identifier,

Trim(SubField(Series, '-', 1)) as [Series Number],

Trim(Mid(Series, Index(Series, '-', 1) + 1)) as [Series Type];

LOAD Identifier,

SubField(Series, '/') as Series;

LOAD * INLINE [

Identifier, Series

100, 1-Orange-ST1 / 4-Purple-ST3 / 10-Yellow-ST6

200, 11 - Green / 60 - Orange

500, 1 - Purple / 4 - Green / 10 - Yellow / 30 - Orange

];

Re: Data parsing challenge

terrusie — Wed, 02 May 2018 17:31:17 GMT

Sunny,

Thanks a bunch!

I had no idea that "Mid" existed. I have to remember that one. I only knew about Subfield, Left, and Right (which all had their quirks when it came to the redundant hyphen).