topic Re: How to compare data from different lines in QlikView

How to compare data from different lines

vchuprina — Wed, 03 Feb 2016 17:23:04 GMT

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

Re: How to compare data from different lines

settu_periasamy — Thu, 04 Feb 2016 05:29:22 GMT

Can you give the sample date with your needed output?

Re: How to compare data from different lines

vchuprina — Thu, 04 Feb 2016 12:18:04 GMT

In output file I should have following:

STYLE COLOR

W255C836A-ONL BLACK

W255C946A GREY

W255C946A WHITE

Re: How to compare data from different lines

swuehl — Thu, 04 Feb 2016 12:23:58 GMT

Are there any rules how the lines can be deciphered?

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

Re: How to compare data from different lines

swuehl — Thu, 04 Feb 2016 12:34:18 GMT

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;

Re: How to compare data from different lines

vchuprina — Thu, 04 Feb 2016 13:44:28 GMT

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

Re: How to compare data from different lines

vchuprina — Thu, 04 Feb 2016 16:07:51 GMT

Thank you, it works perfectly.