- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Applymap using a subfield
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],
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!!