Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vchuprina
New 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
MVP
MVP

Re: How to compare data from different lines

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;

6 Replies

Re: How to compare data from different lines

Can you give the sample date with your needed output?

vchuprina
New Contributor III

Re: How to compare data from different lines

In output file I should have following:

STYLE                           COLOR

W255C836A-ONL            BLACK

W255C946A                    GREY

W255C946A                    WHITE

MVP
MVP

Re: How to compare data from different lines

Are there any rules how the lines can be deciphered?

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

MVP
MVP

Re: How to compare data from different lines

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
New Contributor III

Re: How to compare data from different lines

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

vchuprina
New Contributor III

Re: How to compare data from different lines

Thank you, it works perfectly.