topic Re: Help with Mapping or alternative in QlikView

Help with Mapping or alternative

craig157 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have two seperate data sources which have different information that I need to relate to each other.

Table1:

Part Description

Estimated Usage

Table2:

Part Number

Real Usage

Now the problem I have is that Table 1 has part description is an annoying format which contains the part number I want to relate.

Example :Table1::Part Description = "BIG THINGY PN 344400" where as Table2::PartNumber = 344400

We have a mapping table that we use throughout the project and was hoping to somehow use this (unfortunately by the previous owner who no longer works for us)

I've created a tab within our mapping spreadsheet to try and convert the 'Part Description' from Table 1 to create a new Field, Just substituing out the last couple of characters. Unfortunately for different parts, there is a different length of part numbers so I am manually altering this.

My Mapping Table looks like the below:

Part Description	Part Number Converted (from Part Description)
BIG THINGY PN 222000	222000
OTHER THINGY SN 39002HHB2	39002HHB2
BLAEXAMPLE PN 3N3EE741000012P01	3N3EE74100012P01

The data from Table 1 is downloaded daily, where as Table 2 is extremely rarely updated.

So my actual question!!

Is it possible, for Table1::PartDescription to be uploaded as the new MappingTable::PartNumberConverted instead using the likes of ApplyMapping?

If this doesn't make sense please just let me know and I will try to explain the best I can.

Re: Help with Mapping or alternative

Anil_Babu_Samineni — Thu, 23 Nov 2017 17:11:12 GMT

Perhaps this?

Table1:

Mapping Load [Part Description],

SubField([Part Description], ' ', -1) as LastName_Part_Description,

[Estimated Usage]

From T1;

Table2:

Load [Part Number],

[Real Usage]

From T2;

Load *, MapSubString('Table2', LastName_Part_Description, [Part Number]) as LastName_Part_Description

Resident Table1;

Re: Help with Mapping or alternative

OmarBenSalem — Thu, 23 Nov 2017 17:13:55 GMT

load text(replace( "Part Description",left("Part Description",limit-1),'') )as newField;

load *,FindOneOf("Part Description", '1234567890') as limit Inline [

Part Description

BIG THINGY PN 222000

OTHER THINGY SN 39002HHB2

BLAEXAMPLE PN 3N3EE741000012P01

];

result:

Re: Help with Mapping or alternative

craig157 — Tue, 28 Nov 2017 08:27:11 GMT

Hi Omar,

That would be great but I will have a couple thousand potential results that are updated every so often.

Cheers

Re: Help with Mapping or alternative

craig157 — Tue, 28 Nov 2017 08:45:20 GMT

Hi Anil,

Struggling to get the last bit working.

Can you please advise what your trying to do in the MapSubString.

It doesn't like having the underlined part number:

MapSubString('Table2', LastName_Part_Description, [Part Number]) as LastName_Part_Description

Re: Help with Mapping or alternative

antoniotiman — Tue, 28 Nov 2017 08:46:13 GMT

What about

Table1:

LOAD

[Part Description],Subfield([Part Description],' ',-1) as [Part Number],

Estimated Usage

From ...

Table2:

Left Join // Is optional : Yes -> 1 Table, No -> 2 Tables

LOAD [Part Number],

[Real Usage]

From ...