topic Re: How load necessary information from unstructured column? in QlikView

How load necessary information from unstructured column?

vchuprina — Tue, 25 Aug 2015 15:50:45 GMT

Hi guys,

In one column I receive different information (Style, size and color). I try to determine size and color.

TAFFY PINK14P 9625423

9699439 ULTRA WHIT 2P

ULTRA WHITXXS 9699439

9699439 ULTRA WHIT0X/1X

I use following code for size:

pick (WildMatch( @13, '0X/1X','2X/3X','XS/S' , '00P', '0', '1X', '2P', '2', '2X' , '3X', '4P', '4' , '6P' , '6', '8P', '8', '10P' ,'10' , '10P', '12'), 0X/1X','2X/3X','XS/S' , '00P', '0', '1X', '2P', '2', '2X' , '3X', '4P', '4' , '6P' , '6', '8P', '8', '10P' ,'10' , '10P', '12' ) AS Size

Maybe you can advise better way.

Re: How load necessary information from unstructured column?

Oleg_Troyansky — Tue, 25 Aug 2015 15:58:11 GMT

Given the data structure, I can't think of a better solution, however this one is too ugly and prone to error - for example, it's enough that product code contains 0, and this formula will assume that 0 is the size.

I'd try hard to obtain better data.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: How load necessary information from unstructured column?

Peter_Cammaert — Tue, 25 Aug 2015 16:05:55 GMT

It will be very difficult to create code that works at all times.

Maybe you'll have to get used to a motto you often hear in the software development world: "Garbage in = garbage out"

Re: How load necessary information from unstructured column?

robert_mika — Tue, 25 Aug 2015 20:11:35 GMT

Hi troyansky,

When do you expect your book to available in Europe?(UK)

Re: How load necessary information from unstructured column?

Oleg_Troyansky — Tue, 25 Aug 2015 21:15:16 GMT

Hi Robert,

it should be already available, as far as I know... If it's not available yet, it's up to the retailers like Amazon.uk etc...

I know for sure that several electronic formats are available both in the US and in Europe.

hope you enjoy the book!

Re: How load necessary information from unstructured column?

Oleg_Troyansky — Tue, 25 Aug 2015 21:18:55 GMT

I just checked at amazon.co.uk, and they are still listing the book as a "pre-order", promising the release date of September 4th.

If they snooze any longer, I could bring a few copies with me to Copenhagen for the Masters Summit participants 🙂

Re: How load necessary information from unstructured column?

robert_mika — Tue, 25 Aug 2015 21:33:06 GMT

Thank you Oleg.

This is the same place I was checking(Amazon).

I will wait for the release.

Copenhagen?

Yes.. but maybe next year..:)

Re: How load necessary information from unstructured column?

MarcoWedel — Wed, 26 Aug 2015 10:56:52 GMT

Hi,

maybe one solution, using a table of sizes, could be also:

tabSizes:
LOAD Text(size) as size INLINE [
    size
    0X/1X
    F2
    2X/3X
    XS/S
    00P
    0
    1X
    2P
    2X
    2
    3X
    4P
    4
    6P
    6
    8P
    8
    10P
    10P
    10
    12
];

mapSizes:
Mapping
LOAD *,
     '@start@'&size&'@end@'
Resident tabSizes;

tabText:
LOAD yourtext,
     Text(TextBetween(size,'@start@','@end@',IterNo())) as size
While IterNo()<=SubStringCount(size,'@start@');
LOAD *,
     MapSubString('mapSizes',yourtext) as size
INLINE [
    yourtext
    TAFFY PINK14P       9625423
    9699439 ULTRA WHIT 2P
    ULTRA WHITXXS        9699439
    9699439 ULTRA WHIT0X/1X
];

hope this helps

regards

Marco