How to use data in table 'AUTOGENERATE(1)'?

[DutchArjo]

While I was thinking how I could get all dates matched/combined with all dates available to check in the Not_present table, I created the script below and using AUTOGENERATE 1 all needed lines were created.

AFWEZIGHEID:
Load
[%MDW_ID] as %MDW_ID,
[%MDW_ID]&'_'&date(afwezig_datum) as mdw_afwezigdate,
afwezig_datum as AFW_datum
FROM [lib://selfservice_29_LBO/BE format/Arjo_Kopie van BE format  2024 test_.xlsx]
(ooxml, embedded labels, table is mdw_afwezigheid);


AANWEZIGHEID:
lOAD
[ID] as %MDW_ID,
[ID] as MDW_ID,
datum as ANW_datum
FROM [lib://selfservice_29_LBO/BE format/Arjo_Kopie van BE format  2024 test_.xlsx]
(ooxml, embedded labels, table is test_dat);

zTemp2:
load [ID]
FROM [lib://selfservice_29_LBO/BE format/Arjo_Kopie van BE format  2024 test_.xlsx]
(ooxml, embedded labels, table is test_dat);

Test:
load [%MDW_ID] resident AANWEZIGHEID;
for each a in FieldValueList('%MDW_ID')

        for each b in FieldValueList('ANW_datum')
            load '$(a)'&'_'&'$(b)' as ANW_aanwezigdate Autogenerate 1;
        next b
next a



drop table zTemp2;

MDW_AANWEZIGHEID:
Load
ANW_aanwezigdate,
lookup('ANW_presentie','ANW_AANWEZIGHID',mdf_afwezigdate,'AFWEZIGHEID')
resident [AUTOGENERATE(1)];

exit script;

However, the data is not in the desired table but in an AUTOGENERATE(1) table.

I try to use this table again for a lookup in the 'Not_present' table, but I get an error:

I bet my code isn't the best or the most efficient but for now, it is the only way I managed to get all the ID's combined with all the dates.

Why do I get an AUTOGENERATE(1) table when I labeled the table 'Test'. And why is the field 'ANW_aanwezigdate in this AUTOGENERATE(1) table instead of the 'Test' table, and why is %MDW_ID in the 'Test' table?

[marcus_sommer]

I think you could get the result in a simpler way, like:

t: load Date1 from X; join(t) load Date2 from Y;

which creates also a cartesian product.