Find value from Substring.

[#1]

Hi

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

[#2]

Hi

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

[sunny_talwar]

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