ApplyMap to same file name record does not work for load *

[#1]

Hi,

I have about 13 files to load. In each file, I have 2 tabs to load. For each row of transaction, I want to insert the first 3 char of the file name as a new column and use applymap to get the full name to replace these three char.

I have used Load directorypath/*.xls to load in all the 13 files, and then used apply map for this new column(value derived from first 3 char of the file name).

However, when the script is run, I realised that the file name is returned as '-'. I have trimmed the spaces and used UPPER before calling applymap but to no avail.

Can anyone help? I have searched the community and saw that this maybe due to calling of multiple files using the wildcard symbol, resulting that the filename is not derived.

Thanks!

[Miguel_Angel_Baeyens]

I may be missing something, so I understand you want to create a new field that stores the name of the field the data is being loaded, right? There is a function for that so

Table:
LOAD *,
     FileBaseName() AS File // will return the name without the extension
FROM *.xls (...);

Now you don't need the mapping table any longer, since you have the full name for each record. But if you still want to get just the first three characters from the file, change the new field to

Table:
LOAD *,
     Left(FileBaseName(), 3) AS File // will return the first three chars from the file name
FROM *.xls (...);

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[SunilChauhan]

hope this help

Tal1_temp:
Load * inline [
a,b,c
1,a1,c1
2,c2,a2
3,c3,a3
];

tab2_temp:

Load * inline [
a,d
1,a41sd
2,b41sd
3,c41sd
];

tab2:
Mapping
Load
a,
left(d,3) as d
resident tab2_temp;

Tab1:
Load *,
applymap('tab2',a) as d
resident Tal1_temp;
drop table Tal1_temp,tab2_temp;

[#4]

This looks good. A quick question - does a gets dropped off when Tab1 is loaded(i.e. only have d.)?

As I would like to keep only the column with the mapping value and drop off the initial input value column.

[#5]

Thanks for your help. The file name does not contain the actual full value - it's in abbreviations. Hence I need to use ApplyMap to get the full value based on the abbreviated file name.

Does ApplyMap work for multiple file load when I use to wild card sign?

[rwunderlich]

Mapping against filenames with wildcard works for me. Can you post the relevant portions of your script (The MAPPING LOAD and the ApplyMap())

-Rob

http://robwunderlich.com

[#7]

Hi Rob,

I have posted a snippet below. Thanks!!

SD_temp:

    LOAD

                UPPER(LEFT(FileBaseName(),3))                                    AS     Code,

        'Subs Volume'                                                    AS    Measure,

        ApplyMap('Map_MeasureGroup','SVOLUME')                        AS    [Measure Group],

        Date(Date#(Date(@1,'YYYY') & Date(@1,'MM'),'YYYYMM'),'YYYYMM')    AS    Period,

        @2                                                                AS    [Issue Date],

        @3                                                                AS    [Issue No],

        @7                                                                AS    UK,

        @12                                                              AS    ROW,

        @17                                                              AS    US_Can

    FROM

    [\\tts\CR Link\00_SReports\*.xls]

SD_temp2:

CrossTable(Country,Value,7)

LOAD

    *

RESIDENT SD_temp;

DROP TABLE SD_temp;

MAINData:

LOAD

    *,

    ApplyMap('Map_Name', Code,'Unknown')                AS FullName

RESIDENT SD_temp2;

DROP FIELD Code;

DROP TABLE SD_temp2;    

[SunilChauhan]

definetely this will work

[#9]

The name was showing up as '-' when I took a look at the table box for the selected data..

[rwunderlich]

It sounds like your tablebox is including values that don't link to anything in the MAINData. What happens if:

1. Don't drop FIELD Code.

2. Make a tablebox of just Code and FullName.

Also, what is the density of the FullName field in the MAINData table? (Hover over the field in the table viewer).

-Rob