topic Re: big problem with variable and set analysi expression in QlikView

big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 10:32:11 GMT

HELLO,
I still have problems with the expression set download from a file, the expression gives the right result using direct in a table,

but when I download it since file does not work, I removed the $ and used TOTAL, and nothing has changed

Re: big problem with variable and set analysi expression

Anil_Babu_Samineni — Thu, 28 Dec 2017 11:00:38 GMT

Can you paste expression?

Re: big problem with variable and set analysi expression

pradosh_thakur — Thu, 28 Dec 2017 11:06:21 GMT

you need to be more clear with your requirement and as anil said please paste the expression.

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:01:55 GMT

sum({<ANNEE = {"=Max(TOTAL (ANNEE))"}, MOIS, MOIS = {"<= MAX(TOTAL(MOIS))"} , DATE_SITUATION,CREDIT_NATURE={"CONSO"},CLIENT_PRE_SEGMENT_A={"HAUT DE GAMME"}>} CREDIT_MONTANT_PRODUCTION )

Re: big problem with variable and set analysi expression

Anil_Babu_Samineni — Thu, 28 Dec 2017 12:05:21 GMT

May be problem due to one field to degrade using multi times

sum({<ANNEE = {"=ANNEE=Max(TOTAL ANNEE)"}, MOIS = {"<=MAX(TOTAL MOIS)"} , DATE_SITUATION,CREDIT_NATURE={"CONSO"},CLIENT_PRE_SEGMENT_A={"HAUT DE GAMME"}>} CREDIT_MONTANT_PRODUCTION )

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:08:17 GMT

Anil expression is correct when i used directly in expression , and when i put this expresion in variable didnt work

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:08:59 GMT

look this exemple with $ :
um(TOTAL{<ANNEE = {$(=Max(ANNEE))}, MOIS, MOIS = {"<= $(=MAX(MOIS))"} , DATE_SITUATION,CREDIT_NATURE={"CONSO"},CLIENT_PRE_SEGMENT_A={"CLASSE MOYENNE"}>} CREDIT_MONTANT_PRODUCTION )

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:09:26 GMT

um(TOTAL{<ANNEE = {$(=Max(ANNEE))}, MOIS, MOIS = {"<= $(=MAX(MOIS))"} , DATE_SITUATION,CREDIT_NATURE={"CONSO"},CLIENT_PRE_SEGMENT_A={"CLASSE MOYENNE"}>} CREDIT_MONTANT_PRODUCTION )

Re: big problem with variable and set analysi expression

PrashantSangle — Thu, 28 Dec 2017 12:11:09 GMT

try below

sum({<ANNEE = {"$(=Max(TOTAL ANNEE))"}, MOIS, MOIS = {"<= $(=MAX(TOTALMOIS))"} , DATE_SITUATION,CREDIT_NATURE={"CONSO"},CLIENT_PRE_SEGMENT_A={"HAUT DE GAMME"}>} CREDIT_MONTANT_PRODUCTION )

Regards,

Re: big problem with variable and set analysi expression

Anil_Babu_Samineni — Thu, 28 Dec 2017 12:11:30 GMT

That means, Let's assume your variable called Var1 and expression contains same. Are you going to use Using Parameter level or Plain variable?

Re: big problem with variable and set analysi expression

sunny_talwar — Thu, 28 Dec 2017 12:12:26 GMT

You can look here for ways to stop dollar sign expansion in the script

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )‌

Avoid Dollar sign expansion when reloading | Qlik Community‌

Variable inside a variable in the load script

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:14:52 GMT

I NEEDTHIS EWPRESSIONsum(TOTAL{<ANNEE = {$(=Max(ANNEE))}, MOIS, MOIS = {"<= $(=MAX(MOIS))"} , DATE_SITUATION,CREDIT_NATURE={"CONSO"},CLIENT_PRE_SEGMENT_A={"CLASSE MOYENNE"}>} CREDIT_MONTANT_PRODUCTION )

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:27:24 GMT

Hello sunny ,
the first ligne is the variable second ligne is the expression

Result

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:30:37 GMT

excel file:

Script:

Indicateur:

LOAD Référence as Référence_Ind ,

Indicateurs AS Indicateurs_Desc

FROM

[..\REF_IND\Spécification Base de données Pilotage Opérationnel.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Référentiel Indicateur]);

//LIST EXPRESSION

Expression_list:

LOAD RowNo() as REF_Exp,

Référence as Référence_Ind,

Indicateurs,

replace(Indicateurs,' ','') & replace(REF_Expression,' ','') as desc_expression,

Segment,

Expression

FROM

[..\REF_IND\Spécification Base de données Pilotage Opérationnel.xlsx]

(ooxml, embedded labels, table is REF_Expression);

LET myCount = peek('REF_Exp',-1,[Expression_list]);

FOR _i = 0 to $(myCount);

LET _varName = Peek('desc_expression',$(_i),[Expression_list]);

LET _varValue = Peek('Expression',$(_i),[Expression_list]);

//Now create the variables

SET [$(_varName)] = $(_varValue);

NEXT _i;

for example Anil this expression

COUNT({< CLIENT_ENTREE={"CONQUETE"},CLIENT_PRE_SOUS_SEGMENT_A,CLIENT_PRE_SOUS_SEGMENT_A,CLIENT_PRE_SOUS_SEGMENT_A={"2 CM - ÉPARGNANT"}>}CLE)

worked without problem

Re: big problem with variable and set analysi expression

sunny_talwar — Thu, 28 Dec 2017 12:31:49 GMT

I know and the problem is that while executing the script, the dollar sign expansion is getting evaluated causing it to come as null. The above posted threads have provided some alternative ways of doing it

Re: big problem with variable and set analysi expression

Anil_Babu_Samineni — Thu, 28 Dec 2017 12:33:29 GMT

So, If you want to use this variable [$(_varName)] in set analysis. I believe this won'r work because Peek() only for Script not UI where i am thinking.

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:34:24 GMT

I tried &chr(36) & as you look in screen

Re: big problem with variable and set analysi expression

sunny_talwar — Thu, 28 Dec 2017 12:35:53 GMT

Would you be able to share your Excel file with just this one variable which is causing problems

Re: big problem with variable and set analysi expression

souadouert — Thu, 28 Dec 2017 12:39:38 GMT

also when i used & '$' &

ok sunny 2minutes

Re: big problem with variable and set analysi expression

pradosh_thakur — Thu, 28 Dec 2017 12:48:02 GMT

make sure you dont have space between " and <= that can cause the sum to be evaluate to zero