Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
DutchArjo
Creator
Creator

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

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.

Knipsel.PNG

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

Knipsel.PNG

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.

Knipsel.PNG

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?

Labels (3)
1 Reply
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.