Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
craig157
Creator II
Creator II

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
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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

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
Creator II
Creator II
Author

Hi Omar,

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

Cheers

craig157
Creator II
Creator II
Author

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

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