Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
craig157
Contributor

Help with Mapping or alternative

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 DescriptionPart Number Converted (from Part Description)
BIG THINGY PN 222000222000
OTHER THINGY SN 39002HHB239002HHB2
BLAEXAMPLE PN 3N3EE741000012P013N3EE74100012P01

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.

5 Replies

Re: Help with Mapping or alternative

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Help with Mapping or alternative

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:

Capture.PNG

craig157
Contributor

Re: Help with Mapping or alternative

Hi Omar,

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

Cheers

craig157
Contributor

Re: Help with Mapping or alternative

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

antoniotiman
Honored Contributor III

Re: Help with Mapping or alternative

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