Data parsing challenge

[terrusie]

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?

[sunny_talwar]

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

];

[terrusie]

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)?

[sunny_talwar · Accepted solution]

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

];

[terrusie]

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).