Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lessassy
Creator
Creator

Invalide Expression in SQL Load Statement

Hello everyone,

I'm working both on oracle and Qliksense and i'm trying to extract datas from oracle.

My script looks the following:

TMP_SOLL:
LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,
COD_RES,
LIB_RES,
COD_SOC,
COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
"COD_SOC",
"COD_DAT_CLO",
"LIB_FAM",
"LIB_NAT",
"LIB_STA",
"LIB_TYP_DEM",
"COD_AGE_TRAI",
"LIB_AGE_RLX",
"COD_SEC_RLX",
"LIB_SEC_RLX",
"COD_INSEE",
"COD_AGE_GEO",
"LIB_AGE_GEO"
FROM "LMH"."V_SOLLI_VS_QS";

 

The first thing i noticed is that it's impossible for me to do a preceding LOAD LIKE

TMP_SOLL:

LOAD*,

Count (RLX) as NB_FILES ;
LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,

(more fields not showed);

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
(more fields not showed);

 

I get an error message saying that the statement is not good.

Then i created another table to do the transformation :

 

SOLL:
LOAD
COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
//Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO,
Count(Trim(Text(COD_RLX))) as COD_RLX // I get an error message when i add the count (Invalid expressiojn)
Resident TMP_SOLL

GROUP BY "COD_RES",
/COD_INSEE",
"COD_AGE_GEO",
 "LIB_NAT",
"LIB_STA",

"ANNEE"

"MOIS"

 

I also get an error message when i add the field in the clause group by .

SOmeone can help me please.

thank you

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

How about this?

TMP_SOLL:
LOAD COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO,
COUNT(COD_RLX) as NB_DOSS
Group by
COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,
COD_RES,
LIB_RES,
COD_SOC,
COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
"COD_SOC",
"COD_DAT_CLO",
"LIB_FAM",
"LIB_NAT",
"LIB_STA",
"LIB_TYP_DEM",
"COD_AGE_TRAI",
"LIB_AGE_RLX",
"COD_SEC_RLX",
"LIB_SEC_RLX",
"COD_INSEE",
"COD_AGE_GEO",
"LIB_AGE_GEO"
FROM "LMH"."V_SOLLI_VS_QS";

View solution in original post

5 Replies
sunny_talwar

What is RLX here? I see COD_RLX, but do you have RLX also?

image.png

Also, you need to add group by in your preceding load... but the way it is structured, you will have a count of 1 for each row because you will be grouping it by all the fields unless you have multiple rows where all columns can repeat. I guess what exactly is your underlying goal here?

lessassy
Creator
Creator
Author

Thank you for your reply.

I just want to do a preceding load but i have issue with the group by i guess:

TMP_SOLL:
LOAD *,
COUNT(COD_RLX) as NB_DOSS
Group by
COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,
COD_RES,
LIB_RES,
COD_SOC,
COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
"COD_SOC",
"COD_DAT_CLO",
"LIB_FAM",
"LIB_NAT",
"LIB_STA",
"LIB_TYP_DEM",
"COD_AGE_TRAI",
"LIB_AGE_RLX",
"COD_SEC_RLX",
"LIB_SEC_RLX",
"COD_INSEE",
"COD_AGE_GEO",
"LIB_AGE_GEO"
FROM "LMH"."V_SOLLI_VS_QS";

 

I get the same message "unvalid expression"

What i did was to use the count expression and put all other fields in the group by but it doesn't work and i don't know why.

 

sunny_talwar

How about this?

TMP_SOLL:
LOAD COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO,
COUNT(COD_RLX) as NB_DOSS
Group by
COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,
COD_RES,
LIB_RES,
COD_SOC,
COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
"COD_SOC",
"COD_DAT_CLO",
"LIB_FAM",
"LIB_NAT",
"LIB_STA",
"LIB_TYP_DEM",
"COD_AGE_TRAI",
"LIB_AGE_RLX",
"COD_SEC_RLX",
"LIB_SEC_RLX",
"COD_INSEE",
"COD_AGE_GEO",
"LIB_AGE_GEO"
FROM "LMH"."V_SOLLI_VS_QS";
lessassy
Creator
Creator
Author

Thank you it worked !

SO basically you just replace the '*' symbol by all the fields, right ?

Can you tell me why it doesn't work with a simple LOAD *, Count(n) as NB_DOSS ?

 

Thank you for your help !!

sunny_talwar

because I don't think when you listed fields .. you actually listed all of them? Did you list COD_RLX? I don't see it... so what that means is that * includes is, but listing the fields explicitly... it did not and that was the difference