topic Re: Inconsistent structure of data in column in QlikView

Inconsistent structure of data in column

vchuprina — Fri, 12 Aug 2016 16:36:30 GMT

Good Morning All,

We receive files and one of columns contains style, color and color code information. The problem that data in this column can be placed by different ways, see examples below:

Examples

Description

C99ELS007 BLACK

STYLE COLOR

C99SE7006-ELKMU-BLACK/MUSLIN

STYLE TRASH COLOR

LP61980MQ-BDH-BLACK-WHITE

STYLE TRASH COLOR

PR6B700DFG-BC-G4432-J-CDB-CHAMBR

STYLE TRASH COLOR__CODE TRASH COLOR

WOMEN-CHAMBR.PR6B700DFG

TRASH COLOR STYLE

PO64301PL.IVORY

STYLE COLOR

ED6SD05NL/BLK/70

STYLE COLOR COLOR__CODE

R263C113E

STYLE

MEN R263C343E.OLIVE

TRASH STYLE COLOR

Do you have any ideas how correctly determine Style, Color code and color description.

Thanks,

Vitaliy

Re: Inconsistent structure of data in column

Gysbert_Wassenaar — Fri, 12 Aug 2016 16:43:08 GMT

Not without one or more tables for each element with the valid values for those elements.

LP61980MQ-BDH-BLACK-WHITE

And that's not STYLE TRASH COLOR, but STYLE TRASH COLOR COLOR.

Re: Inconsistent structure of data in column

vchuprina — Fri, 12 Aug 2016 16:58:34 GMT

Hi Gysbert,

As I understand you mean create tables with correct values and then compare it with source files?

BTW, BLACK-WHITE is one color

Re: Inconsistent structure of data in column

Gysbert_Wassenaar — Fri, 12 Aug 2016 17:01:47 GMT

Yes. That seems to be the only way. Check each value from those tables with the weird strings to see if any value matches. Or do you have an algorithm that can parse such a string and spit out the correct parts?

Re: Inconsistent structure of data in column

vchuprina — Fri, 12 Aug 2016 17:13:15 GMT

At the first time when I had a few variants for example ED6SD05NL/BLK/70 and PO64301PL.IVORY

I tried use something like this:

IF (substringcount([Style], '.') >0, Subfield([Style], '.', 1),

IF (substringcount([Style], '/') >0, Subfield([Style], '/', 1) )) AS STYLE

But it became difficult to use this way when we I started getting a lot of variants.

So I can't say that I have an algorithm

Re: Inconsistent structure of data in column

Gysbert_Wassenaar — Fri, 12 Aug 2016 17:15:41 GMT

Totally randomly put together then. Is there always a non alphanumeric character between two elements? I see dots, spaces and slashes used. Or are elements sometimes concatenated together directly too?

edit: I think I already see the next problem: J-CDB itself contains a character that's also used as a separator. Looks like bloody useless data tbh. Spank the fool that thought up this idiocy.

Re: Inconsistent structure of data in column

vchuprina — Fri, 12 Aug 2016 17:25:06 GMT

Yes sometimes two elements can be combined.

For example C99ELS007BLACK

Re: Inconsistent structure of data in column

Gysbert_Wassenaar — Fri, 12 Aug 2016 17:29:14 GMT

Bah. Then only brute force matching of substrings remains and you'd better hope that no substring value ever matches or is part of another substring value.

Re: Inconsistent structure of data in column

jpenuliar — Fri, 12 Aug 2016 17:41:56 GMT

Hi Vitaliy,

In your other Columns, do you have any associative information like Manufacture, Model, product, supplier etc...

Based on my experience, i noticed different Manufacturer or supplier tend to use their own naming conventions.

I had a similar case as you did, yours is more complex. I ended up building a lookup table (by script and manually).

you must have other data sources which you can pull these info from

Re: Inconsistent structure of data in column

vchuprina — Mon, 15 Aug 2016 15:55:48 GMT

Hi Jonathan,

Yes, I have such information.