topic Re: Find value from Substring. in QlikView

Find value from Substring.

Tue, 08 Nov 2016 10:17:28 GMT

I have this table that I read in to QlickView from Excel:

TABLE 1

ItemName	ItemNumber
CFC-B1-305x305x80-100	1111
CFC-B1-457x457x80-200	2222

And this from another Excel sheet:

TABLE 2

ID	Value
0	305x305x80
1	457x457x80

From table1 Itemname i need to extract part of the value and find a match in table 2 field VALUE and get the ID that I need to use in a tableObject I create later in the result screen where I create a string with the ID i find like this:

ItemName	ItemNumber	string
CFC-B1-305x305x80-100	1111	filterdimension=0
CFC-B1-457x457x80-200	2222	filterdimension=1

I have this in the load:

// Translation file CFC

table1:

LOAD id,

value

FROM

(ooxml, embedded labels, table is cfc_filt_dimen);

Table2:

//---- CFC-----

LOAD 'CFC' as product,

fam,

itemName,

m3ItemNumber

FROM

(ooxml, embedded labels, table is itemNumber);

Please help

Regards

Göran

Re: Find value from Substring.

Tue, 08 Nov 2016 11:09:50 GMT

I added one more line but the result is wrong. The first row get correct value, but every other get the same as the first row.

// Translation file CFC

table1:

LOAD id,

value

FROM

(ooxml, embedded labels, table is cfc_filt_dimen);

Concatenate

Table2:

//--------------------------

//---- CFC-----

LOAD 'CFC' as product,

fam,

itemName,

NEW LINE----> exists(id, SubField(itemName, '-', 3)) as Newfield,

m3ItemNumber

FROM

(ooxml, embedded labels, table is itemNumber);

LOAD attributeId,

attributeDescription,

attributeValues,

externalAttributeValues

FROM

(ooxml, embedded labels, table is productAttributes);

//END ----------CFC------

Re: Find value from Substring.

sunny_talwar — Tue, 08 Nov 2016 12:11:08 GMT

May be this:

MappingTable:

Mapping

LOAD Value,

'/' & ID & '\'

FROM

[https://community.qlik.com/thread/239032]

(html, codepage is 1252, embedded labels, table is @2);

Table:

LOAD ItemName,

ItemNumber,

'filterdimension=' & TextBetween(MapSubString('MappingTable', ItemName), '/', '\') as String

FROM

[https://community.qlik.com/thread/239032]

(html, codepage is 1252, embedded labels, table is @1);