What is the reason I cannot use a just loaded field in load script?

[DutchArjo]

I have this part in the load script:

ztemp3:
NoConcatenate
load
*,
%MDW_ID&'_'&CAL_Date as Datumsleutel,
lookup('MDW_Afwezigheid','DatumSleutel',DatumSleutel,'AFWEZIGHEID')
resident ztemptest;

This results in this error: DatumSleutel not found.

However, I have loaded the field before the lookup function.

What is the reason it cannot be used in the lookup right after loading it? Is it because the field DatumSleutel does not exist in the table ztemptest?

[hic · Accepted solution]

Yes, you can only use fields that exist as INPUT to the Load statement. Otherwise you would get ambiguities. Consider for instance

Load
a as b,
b as c
From …

If you now add an additional field
b as d
it would be unclear what "b" refers to: 'a as b', or 'b as c'.

Do the following instead:

ztemp3:
NoConcatenate
Load *, lookup('MDW_Afwezigheid','DatumSleutel',DatumSleutel,'AFWEZIGHEID');
Load *, %MDW_ID&'_'&CAL_Date as Datumsleutel Resident ztemptest;

[DutchArjo]

Thanks,

I also tried and found out it works:

if(isnull(lookup('MDW_Afwezigheid','DatumSleutel',%MDW_ID&'_'&CAL_Date,'AFWEZIGHEID')),'v','x') as MDW_Aanwezigheid

I was so convinced I could not use a combination of fields that I did not even try it.

[DutchArjo]

Additional Question:

I used this script (which biggest part I got from @marcus_sommer)

test1:
LOAD %MDW_ID,

Date(Start_date + IterNo() - 1) as WORK_Date

While Start_date + IterNo() - 1 <= End_date;

LOAD * resident zTemp2;

Which gives me this table:

Which is perfect for me for getting the dates people have been working.

Additionally I want to check for the combination of both fields if they exist in the 'absence' table (and use an 'if' function to enter a specific value when true of false in a new field/column (called 'MDW_Aanwezigheid').

However, I keep struggling on how to lookup the entries in the absence table:

I tried the lookup function but I get errors.

I managed to get what I wanted by creating a new table, loading the data and then performing the lookup:

test1:
LOAD %MDW_ID,

Date(Start_date + IterNo() - 1) as WORK_Date

While Start_date + IterNo() - 1 <= End_date;

LOAD * resident zTemp2;

test2:
load *, if(isnull(lookup('MDW_Afwezigheid','DatumSleutel',%MDW_ID&'_'&WORK_Date,'AFWEZIGHEID')),'v','x') as MDW_Aanwezigheid
resident test1;

drop table test1;

but I bet it can be done more efficient. However, I cannot come up how I can do this.

[marcus_sommer]

I doubt that I would use lookup() in such scenario else creating an appropriate mapping, like:

m: 
mapping load %MDW_ID & '_' & Date(Start_date + IterNo() - 1), 'x'
resident zTemp2 While Start_date + IterNo() - 1 <= End_date;

and calling it per:

applymap('m', DatumSleutel, 'v')

[DutchArjo]

I will test this today, thank you.

I the Qlink Sense documentation (https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/use-mapping-as-...), update may 2024, about the mapping function, they state this:

The mapping table, which we name MapCustomerIDtoCountry, is defined in the script as follows:
     MapCustomerIDtoCountry:
     Mapping LOAD CustomerID, Country From Customers ;
     

The next step is to apply the mapping, by using the ApplyMap function when loading the order table:
Orders:
     LOAD *,
          ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country
          From Orders ;
    

twice, they do a 'load ..from table' but when I try to do it like this, it doesn't work. They refer to a previously loaded table with the 'from' statement. But is that correct? As far as I see, the 'resident' statement is for referring to a previously loaded table (https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularSt...), isn't it?

[marcus_sommer]

A mapping requires it quite often that a table/source is loaded twice / several times because it's not a normal table else contains only two columns - the lookup- and the return-value. If the relevant data were already loaded they might be loaded from there per resident but in many cases it's also fine to load it from the origin source.

The shown example from the help will be working - if not you made any mistake. Instead of using null() as third parameter of the applymap() you may return further information from the calling record and/or any kind of check-logic, maybe like:

ApplyMap('MapCustomerIDtoCountry', CustomerID,
   'ID: ' & CustomerID & ' - num: ' & isnum(CustomerID)) as Country

[DutchArjo]

I have this:

MDW_AANWEZIGHEID:
load *,
applymap('m', %MDW_ID&'_'&WORK_Date, 'v') as MDW_Aanwezigheid
from MDW_AANWEZIGHEID;

I get this:

Cannot open file: '\\QAR70093\QlikShare\Apps\MDW_AANWEZIGHEID' (System error: ***)

I will check where I made a mistake.

But if I try the same regarding to the tablenames:

Orders:
     LOAD *,
          ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country
          From Orders ;

this means there already was a table Orders, right?

When I do this in my case:

MDW_AANWEZIGHEID:
load *,
applymap('m', %MDW_ID&'_'&WORK_Date, 'v') as MDW_Aanwezigheid
resident MDW_AANWEZIGHEID;

I get a new table with the name MDW_AANWEZIGHEID followed by _ and a nummer:

"MDW_AANWEZIGHEID_11" for example.

As pointed out, I cannot use 'From' but I can only use 'resident'. So I'm doing something wrong...

[marcus_sommer]

It's not clear what you are doing - but your issues have nothing to do with using any mapping. If tables were already loaded you could access them per resident and if not you need to specify the external source within the from statement.

You may get unexpected tables if you use any kind of loops, wildcards and/or qualify-statements. I suggest to put an exit script; after your first load-statement and then looking within the table-viewer how many tables are there and which names and content they have - and if it are the expected results. If yes, cut the exit script; and put it after the next load-statement and so on ... and if not, correct the load-statement.