topic Re: Applymap using a subfield in QlikView

Applymap using a subfield

Wed, 25 Nov 2020 16:16:04 GMT

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],

Re: Applymap using a subfield

sunny_talwar — Wed, 10 Aug 2016 22:24:28 GMT

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]

Re: Applymap using a subfield

Wed, 10 Aug 2016 22:30:05 GMT

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

Re: Applymap using a subfield

sunny_talwar — Thu, 11 Aug 2016 09:03:41 GMT

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?

Re: Applymap using a subfield

Thu, 11 Aug 2016 17:06:06 GMT

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]);

Re: Applymap using a subfield

Fri, 12 Aug 2016 17:15:21 GMT

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