Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

For loop retrieving same S/N twice

Hi all,

I am using FOR syntax to load N times a fact table (FROM YEAR 2016 TO YEAR 2031), each time using a different WHERE clause. The result expected is to have different S/N numbers with new information calculated (I will show the steps below), but it is not happening. So let's see below what it is expected to happen and what has been done so far.

FACT_1

S/NREGIONFLAG_XFLAG_YDATE_DELAGE (YEARS)PROGRAMBEGINEND
1AAYES

20/10/2010

5
2BBYESYES15/05/200016
3BBYES20/10/20087STD01/01/201631/12/2017
4AA15/05/20106
5CCYESYES20/12/20150STD16/06/201615/06/2018
6BBYES01/01/20151
7AAYES20/02/20106STD16/06/201615/06/2018
8CCYES02/10/20123
9CCYES08/06/20151STD16/06/201615/06/2018

10

AA20/02/200016
11BB15/04/200016
12CC05/05/20170
13BBYES09/12/2019-3
14CC10/10/20105
15CCYES10/10/20105
16CCYES11/10/20105
17CC12/10/20105

TABLE_PERCENTAGE

REGIONPERCENTAGE
AA50
BB75
CC50

RESULT

IDS/NREGIONFLAG_XFLAG_YDATE_DELAGE (YEARS)PROGRAMBEGINEND
11AAYESYES20/10/20105STD21/06/201620/06/2018
26BBYESYES01/01/20151STD21/06/201620/06/2018
314CCYES10/10/20105STD21/06/201620/06/2018
416CCYESYES11/10/20105STD21/06/201620/06/2018
513BBYESYES09/12/20190STD09/04/202008/04/2022
63BBYESYES20/10/20087STD01/01/201631/12/2017
75CCYESYES20/12/20150STD16/06/201615/06/2018
87AAYES20/02/20106STD16/06/201615/06/2018
99CCYES08/06/20151STD16/06/201615/06/2018

The RESULT table is composed by new capture of s/n and the ones already captured.
So the first FOR applies the same percentage from 2016 to 2031 and the second FOR applies the percentage according to region. This percentage is to load a sample of the fact table.




The syntax is shown below:

//calculating the percentages per Year

For  vYEARL = $(vYearStart) to $(vYearEnd);  //2016 to 2031

    IF  '$(vYEARL)'= 2016 then

        LET vNAME = 'FACT_1';

    ELSEIF  '$(vYEARL)'<> 2016 then

        LET vNAME = '$(vNameLATE)';

    ENDIF

//loading table with results of percentage

For a = 0 to NoOfRows('TABLE_PERCENTAGE')-1

  LET vPercenL  = replace((peek('PERCENTAGE',a,'TABLE_PERCENTAGE'))/100,',','.');

  LET vPER_REG_L  = peek('REGION',a,'TABLE_PERCENTAGE');

  LET vPercentage = $(vPercenL);

  //STD percentage

  IF len('$(vPercentage )')=0 then

  vPercentage = 0.3;

  ENDIF

  

    //Generating a consolidated table with S/N after percentages

  LATE_1:

  sample $(vPercentage) load  right([S/N],8)            as [S/N],

    'STANDARD'                            as PROGRAM_TMP

      if(Year(DATE_DEL)<'$(vYEARL)', Today(), AddMonths(DATE_DEL, 4))as BEGIN_TMP,

      if(Year(DATE_DEL)<'$(vYEARL)', AddYears(Today()-1,5),

          AddYears(AddMonths(DATE_DEL-1, 4),5)) as END_TMP,

    'YES'      as FLAG_YY

  resident '$(vNAME)'

  where REGION ='$(vPER_REG_L)' and FLAG_Y<>'YES'  and (('$(vYEARL)'-Year(DATE_DEL))>-1 and  ('$(vYEARL)'-Year(DATE_DEL))<10);

next

  LET vNameLATE = 'LATE_TEST';

    IF  '$(vYEARL)'<>2016 then

    LET vNameLATE = 'LATE_TEST'&'$(vYEARL)';

    ENDIF

    NoConcatenate

    TEST:

    load *

    resident LATE_1;

    drop table LATE_1;

  TESTJOIN:

    join('$(vNAME)')

  load *

  resident TEST;

  drop table TEST;

  

  NoConcatenate

  $(vNameLATE):  //RESULT

  load  [S/N],

  MODEL,

  REGION

  if(FLAG_YY='YES', FLAG_YY, FLAG_Y) as FLAG_Y,  //if the S/N already is flagged with yes, it keeps its original value

  if(FLAG_YY='YES', PROGRAM_TMP, PROGRAM) as PROGRAM,

  if(FLAG_YY='YES', BEGIN_TMP, BEGIN) as BEGIN,

  if(FLAG_YY='YES', END_TMP, END) as END,

  DATE_DEL


  Resident '$(vNAME)';

  drop table '$(vNAME)';

next vYEARL

exit script;

My problem with this syntax is that even by replacing the names os fields, the result is shown duplicated S/N.

I would appreciate your help.

Thanks!

3 Replies
Digvijay_Singh

Can you share sample source data, expected output and the full script. If it is already shared then please confirm which is what, I see few variables like $(vYearStart) etc may be created outside. Just wanted to run shared script using sample data in debug mode.

Anonymous
Not applicable
Author

Hi,

The $(vYearStart) = 2016 and $(vYearEnd) = 2031.

The desirable output is the RESULT table in the first post and also the  $(vNameLATE) table in the script (which will have the name of LATE_12031)

If you have any question, please let me know.

Thanks

Digvijay_Singh

Your current script is identifying missing FLAG_Y records based on conditions(As per where clause), the matched 9 records are filled with FLAG_Y='YES' and BEGIN and END dates. It is making changes in the source file so all the 17 records will be there in $(vNameLATE), the result table.

I am not able to understand the logic behind the RESULT table you have shown as it is showing some of not changed record(already having FLAG_Y='YES') and showing some changed records where FLAG_Y was blank.

I may help on this if I can understand the logic behind the output desired.

Just sharing the script I have been using (Made minor changes in your script, as MODEL field was throwing error, also

statement - IF len('$(vPercentage )')=0 then

wasn't working as there was a space after the variable name.