Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use Mapping load to add field in the table

Hi,

I want to extract a part from the name of the file xlsx and add it as a field in the table using mapping.

In fact, I want to read the second part of the name of the file and change it, then I have to add it as a field in the table:

The name of my file is: "Règlements Bel 22022014.xlsx", I have to extract "Bel" and convert it into "Belgique" and add it as field "country" in the table.

I did this, it is correct?

PaysBel_Map:

Mapping load * ,

subfield(FileName(), ' ',2) as 'Belgique'

from [..\..\Réglements BEL 20022014.xls] ;

Réglement:

Load *,

     ApplyMap ('PaysBel_Map','Belgique') as Country

FROM

[..\..\Réglements BEL 20022014.xls] (biff, embedded labels, table is A$);

Then I have to extract the third part which is the date and add it in the table as field "Date"

How can I do this?

Help me please

2 Replies
Not applicable
Author

maybe:

right( subfield(FileName(),'.',1),8)

mov
Champion III
Champion III

To use applymap, you need to have a map first.  There is no way the script can guess that 'BEL' means 'Belgique' unless you tell this in a mapping table, for example:

CountryMap:
MAPPING LOAD * INLINE [
A, B
BEL, Belgique
FRA, France
...
];

Réglement:
LOAD
*,
applymap('CountryMap', subfield(FileName(), ' ',2)) as Country,
date(date#(replace(subfield(FileName(), ' ',3), '.xls',''),'DDMMYYYY')) as Date
FROM [..\..\Réglements BEL 20022014.xls] (biff, embedded labels, table is A$);