topic Unknown LOAD statement error when trying to do cartesian product in QlikView

Unknown LOAD statement error when trying to do cartesian product

Amstelsus — Sat, 16 Nov 2024 20:53:15 GMT

Hello,

I've been trying to do a cartesian join between two tables in qliksense, example :

dim_temps :

KD_TEMPS	JOUR_OUVRE
2019-01-01	0
2019-01-02	1
2019-01-03	1
2019-01-04	1

fait_timeit:

KC_USER_ID	KD_TEMPS	DN_NB_JOURS_PRESENCE
AAAA	2019-01-02	1
BBBB	2019-01-03	1

Cartesian product :

KC_USER_ID_ACHS	KD_TEMPS_ACHS	DN_NB_JOURS_PRESENCE_ACHS	JOUR_OUVRE	JOUR_ACHS
AAAA	2019-01-02	1	1	0
BBBB	2019-01-03	1	1	0
AAAA	2019-01-01	NULL	0	0
AAAA	2019-01-03	NULL	1	1
AAAA	2019-01-04	NULL	1	1
BBBB	2019-01-01	NULL	0	0
BBBB	2019-01-02	NULL	1	1
BBBB	2019-01-04	NULL	1	1

this is the script I've tried to use to get this result :

Temp:
Load
KD_TEMPS AS KD_TEMPS_ACHS,
JOUR_OUVRE
Resident dim_temps;
LEFT JOIN(Temp)
Load
KC_USER_ID, DN_NB_JOURS_PRESENCE as DN_NB_JOURS_PRESENCE_ACHS
Resident fait_timeit;
fait_timeit_achs:
LOAD
KC_USER_ID as KC_USER_ID_ACHS,
KD_TEMPS_ACHS,
DN_NB_JOURS_PRESENCE_ACHS,
JOUR_OUVRE,
if(DN_NB_JOURS_PRESENCE_ACHS <> 1 and JOUR_OUVRE = 1, 1, 0) as JOUR_ACHS
Resident Temp;
DROP Table Temp;

Using this script, I get this error : Unknown LOAD statement error

What is wrong with my script ? How can I perform a cartesian product correctly ? Thanks

Re: Unknown LOAD statement error when trying to do cartesian product

dapostolopoylos — Mon, 13 May 2019 10:08:56 GMT

You have an error on the second Resident load, replace your script with this:

Temp:
Load
KD_TEMPS AS KD_TEMPS_ACHS,
JOUR_OUVRE
Resident dim_temps;
LEFT JOIN(Temp)
Load
KC_USER_ID, DN_NB_JOURS_PRESENCE as DN_NB_JOURS_PRESENCE_ACHS
Resident fait_time;
fait_timeit_achs:
LOAD
KC_USER_ID as KC_USER_ID_ACHS,
KD_TEMPS_ACHS,
DN_NB_JOURS_PRESENCE_ACHS,
JOUR_OUVRE,
if(DN_NB_JOURS_PRESENCE_ACHS <> 1 and JOUR_OUVRE = 1, 1, 0) as JOUR_ACHS
Resident Temp;
DROP Table Temp;

Re: Unknown LOAD statement error when trying to do cartesian product

jaibau1993 — Mon, 13 May 2019 10:09:58 GMT

Hi!

The sintax seems correct... are you sure that the error raises in this part of the code? I would try to add "NoConcatenate" wherever possible to avoid unintended concatenations:

Temp:
NoConcatenate Load
KD_TEMPS AS KD_TEMPS_ACHS,
JOUR_OUVRE
Resident dim_temps;
LEFT JOIN(Temp)
Load
KC_USER_ID, DN_NB_JOURS_PRESENCE as DN_NB_JOURS_PRESENCE_ACHS
Resident fait_timeit;
fait_timeit_achs:
NoConcatenate LOAD
KC_USER_ID as KC_USER_ID_ACHS,
KD_TEMPS_ACHS,
DN_NB_JOURS_PRESENCE_ACHS,
JOUR_OUVRE,
if(DN_NB_JOURS_PRESENCE_ACHS <> 1 and JOUR_OUVRE = 1, 1, 0) as JOUR_ACHS
Resident Temp;
DROP Table Temp;

If nothing works, could you share a sample of the QV to work with?

Regards,

Jaime.

Re: Unknown LOAD statement error when trying to do cartesian product

Amstelsus — Mon, 13 May 2019 12:15:29 GMT

My bad, already put "it" 😕 still doesn't work