Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
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;

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

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

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

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