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

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

Fri, 05 Aug 2011 11:05:48 GMT

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!

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

Miguel_Angel_Baeyens — Fri, 05 Aug 2011 11:48:25 GMT

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

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

SunilChauhan — Fri, 05 Aug 2011 11:51:48 GMT

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;

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

Fri, 05 Aug 2011 15:11:06 GMT

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.

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

Fri, 05 Aug 2011 15:13:02 GMT

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?

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

rwunderlich — Fri, 05 Aug 2011 15:40:32 GMT

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

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

Fri, 05 Aug 2011 15:57:29 GMT

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;

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

SunilChauhan — Fri, 05 Aug 2011 16:01:59 GMT

definetely this will work

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

Fri, 05 Aug 2011 16:05:41 GMT

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

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

rwunderlich — Fri, 05 Aug 2011 18:28:16 GMT

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

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

Mon, 08 Aug 2011 13:32:20 GMT

I kept the FIELD code and the codes have been read in. But apparently the applymap function didnt seem to be working and I am not sure yet why it's not working.
I hovered over the field in the table viewer but I am not sure what I am supposed to be looking out for.

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

Mon, 08 Aug 2011 14:05:36 GMT

Try ApplyMap before CrossTable.

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

Mon, 08 Aug 2011 14:45:04 GMT

I just tried it out but it's not working.

Just attempted to create another sheet of mapping table and reduce to two columns but it's still not showing up.

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

Mon, 08 Aug 2011 14:53:08 GMT

The strange thing that it's showing up as a dash sign. In my mapping code, I asked to be returned unknow+ unmapped code. But I didnt get anything. Hence I am suspecting that the mapping table function is somehow not working as it should be.

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

Mon, 08 Aug 2011 15:37:52 GMT

Thanks to everyone's help. I took a look at the log when the script was running and realised that indeed, the mapping table was not loaded.

Upon closer examination, I spotted the bug. There was a lack of semi-colon on the last line in the main tab. As such, the first mapping table was not loaded.

And crosstable is not affected by the sequence of applymap and Applymap does run in a loop when I use the wildcard sign.

Thanks again, everyone!