Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

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

1 Solution

Accepted Solutions
Highlighted

Re: Invalide Expression in SQL Load Statement

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
Highlighted

Re: Invalide Expression in SQL Load Statement

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?

Highlighted
New Contributor III

Re: Invalide Expression in SQL Load Statement

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.

 

Highlighted

Re: Invalide Expression in SQL Load Statement

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

Highlighted
New Contributor III

Re: Invalide Expression in SQL Load Statement

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 !!

Highlighted

Re: Invalide Expression in SQL Load Statement

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