Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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";
What is RLX here? I see COD_RLX, but do you have RLX also?
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?
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.
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";
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 !!
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