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: 
souadouert
Specialist
Specialist

big problem with variable and set analysi expression

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


capture1.PNG

31 Replies
souadouert
Specialist
Specialist
Author

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 )

souadouert
Specialist
Specialist
Author

Hello sunny ,
the first ligne is the variable second ligne is the expression
cap2.PNG
Result
Capture.PNG

souadouert
Specialist
Specialist
Author

excel file:excel.PNG

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

sunny_talwar

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

Anil_Babu_Samineni

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
souadouert
Specialist
Specialist
Author

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

sunny_talwar

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

souadouert
Specialist
Specialist
Author

also when i used & '$' &

cap2.PNG

ok sunny 2minutes

pradosh_thakur
Master II
Master II

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

Learning never stops.