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: 
eva_bornaccini
Contributor
Contributor

Peek function in a do while loop

Hi All!

I used several times peek function but in this case i am not able to let it works!

Here is my code,I'm trying to loop a calculation until all values in New_TEMPO_BRAND_SITE_3 are positive.

All seems to work except the assignment of v_iteration, always null 😞

Thank you in advance for any suggestion!!

INTERVALLO:
LOAD DISTINCT
MAX(KEY_TABLE) AS MASSIMO,
MIN(KEY_TABLE) AS MINIMO
Resident NEW_TEMPO_RICALCOLO
WHERE FLG_MONTH_BRAND='1';


lET v_minimo = Peek('MINIMO',0,'INTERVALLO');
lET v_massimo = Peek('MASSIMO',0,'INTERVALLO');

for s=v_minimo to v_massimo;

Set a=0;
set e_flag=0;

Do while e_flag=0;

set e_flag=1;

TEMPO_WHILE_BRAND_TMP_$(s):

LOAD distinct
KEY_TEMPO,
YEAR_MONTH_NATION,
TODAY_CHECK_BRAND,
TIME_BRAND_SITE_VISITS,DATE_2,
GG_BRAND,
KEY_TABLE,
Consensus_BRAND_SITE+(Consensus_BRAND_SITE*$(a)) as NEW_Consensus_BRAND_SITE,

IF(TODAY_CHECK_BRAND<2,BRAND_SITE_VISITS_ACT/(Consensus_BRAND_SITE*(1+$(a)))) AS BRAND_SITE_LESS_2
Resident NEW_TEMPO_RICALCOLO

where KEY_TABLE='$(s)' ;

LEFT JOIN ('TEMPO_WHILE_BRAND_TMP_$(s)')

LOAD DISTINCT
YEAR_MONTH_NATION,
SUM(BRAND_SITE_LESS_2) AS SOMMA_BRAND_ACT,
SUM(TIME_BRAND_SITE_VISITS) AS SOMMA_BRAND_OLD

Resident TEMPO_WHILE_BRAND_TMP_$(s)
WHERE TODAY_CHECK_BRAND<2
GROUP BY YEAR_MONTH_NATION;

LEFT JOIN ('TEMPO_WHILE_BRAND_TMP_$(s)')
LOAD DISTINCT
KEY_TEMPO,
NEW_Consensus_BRAND_SITE,
KEY_TABLE,
IF(TODAY_CHECK_BRAND<2,BRAND_SITE_LESS_2,
TIME_BRAND_SITE_VISITS-(SOMMA_BRAND_ACT-SOMMA_BRAND_OLD)/(DAY(MonthEnd(Date(Date#(DATE_2,'YYYYMMDD'),'MM/DD/YYYY')))-GG_BRAND)) AS New_TEMPO_BRAND_SITE_2,
if(replace((IF(TODAY_CHECK_BRAND<2,BRAND_SITE_LESS_2,
TIME_BRAND_SITE_VISITS-(SOMMA_BRAND_ACT-SOMMA_BRAND_OLD)/(DAY(MonthEnd(Date(Date#(DATE_2,'YYYYMMDD'),'MM/DD/YYYY')))-GG_BRAND))),',','.')<0,1,0) as check,
replace((IF(TODAY_CHECK_BRAND<2,BRAND_SITE_LESS_2,
TIME_BRAND_SITE_VISITS-(SOMMA_BRAND_ACT-SOMMA_BRAND_OLD)/(DAY(MonthEnd(Date(Date#(DATE_2,'YYYYMMDD'),'MM/DD/YYYY')))-GG_BRAND))),',','.') AS New_TEMPO_BRAND_SITE_3
RESIDENT TEMPO_WHILE_BRAND_TMP_$(s);

let v_table= 'TEMPO_WHILE_BRAND_TMP_$(s)';

Let vNoOfRows = NoOfRows('$(v_table)');

for i=0 to vNoOfRows;
let v_name= chr(39)&'TEMPO_WHILE_BRAND_TMP_'&$(s)&chr(39);
Let v_iteration = Peek('New_TEMPO_BRAND_SITE_3', $(i),$(v_name));

if $(v_iteration)<0 then

set e_flag = '0';

end if
next i;
Let a= $(a) + 0.1;
Loop;

Labels (1)
6 Replies
sunny_talwar

Would you be able to share a sample to test this out?

eva_bornaccini
Contributor
Contributor
Author

Hi Sunny, here data and QVW. I tryed to upload data through load inline but maybe (same problem as mine) something doesn't work in resident inside for and while loop.

 

sunny_talwar

What is the expected output based on the sample provided here?

eva_bornaccini
Contributor
Contributor
Author

All values in New_TEMPO_BRAND_SITE_3 must be positive. And to let it happends NEW_Consensus_BRAND_SITE should increase thanks to while loop.

But peek function doesn't work and while loop exit atthe first round 

 

sunny_talwar

I don't think I follow along your script.... that is why I asked you to provide me the output so that I can understand the script or create one which might work based on the output needed. When I say the expected output, I mean the exact numbers you are hoping to see based on the sample provided.....

eva_bornaccini
Contributor
Contributor
Author

Here u are