Applymap using a subfield

[#1]

Hi,

I'm having a little bit of difficulty.  I've used a subfield to isolate some text, and I want to use that new text for an applymap.  How is this possible to do?

ARDetail:
LOAD
[Item: Internal ID],
Name,
Subfield(Name, ' ',1) as [Customer #],
ApplyMap('CustomerNameLong',[Customer #],'Loading Error - Customer Name (Long)') as [Customer Name (Long)],
ApplyMap('CustomerNameShort',[Customer #],'Loading Error - Customer Name (Short)') as [Customer Name (Short)],
ApplyMap('CombinedName',[Customer #],'Loading Error - Combined Name') as [Combined Name],

[sunny_talwar · Accepted solution]

Try this:

ARDetail:

LOAD *,

ApplyMap('CustomerNameLong',[Customer #],'Loading Error - Customer Name (Long)') as [Customer Name (Long)],
ApplyMap('CustomerNameShort',[Customer #],'Loading Error - Customer Name (Short)') as [Customer Name (Short)],
ApplyMap('CombinedName',[Customer #],'Loading Error - Combined Name') as [Combined Name];

LOAD
[Item: Internal ID],
Name,
Subfield(Name, ' ',1) as [Customer #],

or use this

ARDetail:

LOAD
[Item: Internal ID],
Name,
Subfield(Name, ' ',1) as [Customer #],

ApplyMap('CustomerNameLong',Subfield(Name, ' ',1),'Loading Error - Customer Name (Long)') as [Customer Name (Long)],

ApplyMap('CustomerNameShort',Subfield(Name, ' ',1),'Loading Error - Customer Name (Short)') as [Customer Name (Short)],

ApplyMap('CombinedName',Subfield(Name, ' ',1),'Loading Error - Combined Name') as [Combined Name]

[#3]

What will my mapping load look like?  currently I have it like this, but I'm not pulling up any results in my table.

Mapping LOAD Distinct
[Customer #],
[Customer Name (Long)]
FROM

[sunny_talwar]

Is your name match with Customer # or SubField(Name, ' ', 1) match with Customer #? Can you share few rows of data? just to see how this looks?

[#5]

The source data that I have includes a customer name, not necessarily the same format as "Customer Name (Long)", so instead of using "Customer Name (Long)" for the lookup item, I'm isolating the "customer #" from the customer name in the source data and using that for the applymap.  The reason for this is because I have other reports in the future that will utilize this same applymap due to  our generic system reports including the "customer #" (always in the same format), whereas "Customer Name (Long)" is not always the same format.

Not really too sure how to upload files here, so I'll include a screen shot of a sample lookup table as the source system data.

Sample lookup table.  The data on this lookup table is not repeated as it was used for a vlookup function in Excel.

Sample source system data.  The data from this system is based on transactions, so customers are repeated multiple times since each row of data represents 1 transaction in the system.

This is what I'm trying to achieve after solving this applymap issue.  I need to fill in those blanks.  The Open Balance field is created through an expression =sum([Open Balance]).

Here is a copy of my edit script in qlikview.

ARDetail:
LOAD
*,
ApplyMap('CustomerNameLong',[Customer #],'Loading Error - Customer Name (Long)') as [Customer Name (Long)],
ApplyMap('CustomerNameShort',[Customer #],'Loading Error - Customer Name (Short)') as [Customer Name (Short)],
ApplyMap('CombinedName',[Customer #],'Loading Error - Combined Name') as [Combined Name];
LOAD
[Item: Internal ID],
Name,
Subfield(Name, ' ',1) as [Customer #],
[Transaction Date],
[Due Date],
[Document Number],
[Transaction Type],
[CB Code],
[P.O. No.],
[Open Balance]
FROM

(ooxml, embedded labels, header is 4 lines, table is [$(Sheet1)]);

Here is a copy of the mapping load.

CustomerNameLong:
Mapping LOAD Distinct
[Customer #],
[Customer Name (Long)]
FROM

(ooxml, embedded labels, table is [CustomerMaster]);

CustomerNameShort:
Mapping LOAD Distinct
[Customer #],
[Customer Name (Short)]
FROM

(ooxml, embedded labels, table is [CustomerMaster]);

CombinedName:
Mapping LOAD Distinct
[Customer #],
[Combined Name]
FROM

(ooxml, embedded labels, table is [CustomerMaster]);

[#6]

Hi,

I figured out the problem.  It was due to my mapping load script being in a tab after my applymap script.  I promoted my applymap script tabs to be in front of the mapping load script tab and it works now...thanks!!!