How to compare data from different lines

[vchuprina]

I receive files that contains Style and color information in different lines, in additional they haven't any original part, that I can use to join data. See example below:

All Sales Regions  -  CONSIGNMENT PHANTOM.RBK\W255C836A-ONL\L/S TOP CAMDEN

All Sales Regions  - CONSIGNMENT PHANTOM.BLACK

All Sales Regions  - CONSIGNMENT PHANTOM.RBK\W255C946A\L/S TOP THEO

All Sales Regions  - CONSIGNMENT PHANTOM.GREY

All Sales Regions  - CONSIGNMENT PHANTOM.WHITE

How in this case load data correctly(style with necessary color)?

[settu_periasamy]

Can you give the sample date with your needed output?

[vchuprina]

In output file I should have following:

STYLE                           COLOR

W255C836A-ONL            BLACK

W255C946A                    GREY

W255C946A                    WHITE

[swuehl]

Are there any rules how the lines can be deciphered?

Something like .RBK denotes a style? Or lines with \...\ denote a style?

[swuehl · Accepted solution]

Maybe something like this to get you started:

INPUT:
LOAD *, If( Wildmatch(Line, '*.RBK*'), Subfield( Line, '\',2 ), Peek(Style)) as Style , If( Wildmatch(Line, '*.RBK*')=0, Subfield( Line, '.',2 )) as Color ;
LOAD *, If( Wildmatch(Line, '*.RBK*'), Rangesum( 1, Peek(StyleNo)),Peek(StyleNo)) as StyleNo;
LOAD * INLINE [
Line
All Sales Regions  -  CONSIGNMENT PHANTOM.RBK\W255C836A-ONL\L/S TOP CAMDEN
All Sales Regions  - CONSIGNMENT PHANTOM.BLACK
All Sales Regions  - CONSIGNMENT PHANTOM.RBK\W255C946A\L/S TOP THEO
All Sales Regions  - CONSIGNMENT PHANTOM.GREY
All Sales Regions  - CONSIGNMENT PHANTOM.WHITE
];


RESULT:
LOAD StyleNo, Color, Style
RESIDENT INPUT
WHERE Len(Trim(Color ));


DROP TABLE INPUT;

[vchuprina]

You are right. If lines contains RBK or  '\ \' it's mean that in this line we have style

[vchuprina]

Thank you, it works perfectly.