Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
Amstelsus
Contributor

Unknown LOAD statement error when trying to do cartesian product

Hello,

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

dim_temps :

KD_TEMPSJOUR_OUVRE
2019-01-010
2019-01-021
2019-01-031
2019-01-041

 

fait_timeit:

KC_USER_IDKD_TEMPSDN_NB_JOURS_PRESENCE
AAAA2019-01-021
BBBB2019-01-031

 

Cartesian product :

 

KC_USER_ID_ACHSKD_TEMPS_ACHSDN_NB_JOURS_PRESENCE_ACHSJOUR_OUVREJOUR_ACHS
AAAA2019-01-02110
BBBB2019-01-03110
AAAA2019-01-01NULL00
AAAA2019-01-03NULL11
AAAA2019-01-04NULL11
BBBB2019-01-01NULL00
BBBB2019-01-02NULL11
BBBB2019-01-04NULL11

 

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

Labels (1)
3 Replies
dapostolopoylos
Creator III

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;
Father/Husband/BI Developer
jaibau1993
Partner - Creator III

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. 

Amstelsus
Contributor
Author

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