Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vchuprina
New Contributor II

How load necessary information from unstructured column?

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.

7 Replies

Re: How load necessary information from unstructured column?

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?

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?

Hi troyansky,

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

Re: How load necessary information from unstructured column?

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?

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?

Thank you Oleg.

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

I will wait for the release.

Copenhagen?

Yes.. but maybe next year..Smiley Happy

Re: How load necessary information from unstructured column?

Hi,

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

QlikCommunity_Thread_177859_Pic1.JPG

QlikCommunity_Thread_177859_Pic2.JPG

QlikCommunity_Thread_177859_Pic3.JPG

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

Community Browser