Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Contributor III
Contributor III

How to compare data from different lines

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

6 Replies
settu_periasamy
Master III
Master III

Can you give the sample date with your needed output?

vchuprina
Contributor III
Contributor III
Author

In output file I should have following:

STYLE                           COLOR

W255C836A-ONL            BLACK

W255C946A                    GREY

W255C946A                    WHITE

swuehl
MVP
MVP

Are there any rules how the lines can be deciphered?

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

swuehl
MVP
MVP

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;

View solution in original post

vchuprina
Contributor III
Contributor III
Author

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

vchuprina
Contributor III
Contributor III
Author

Thank you, it works perfectly.