For loop retrieving same S/N twice

[Anonymous]

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/N	REGION	FLAG_X	FLAG_Y	DATE_DEL	AGE (YEARS)	PROGRAM	BEGIN	END
1	AA	YES		20/10/2010	5
2	BB	YES	YES	15/05/2000	16
3	BB		YES	20/10/2008	7	STD	01/01/2016	31/12/2017
4	AA			15/05/2010	6
5	CC	YES	YES	20/12/2015	0	STD	16/06/2016	15/06/2018
6	BB	YES		01/01/2015	1
7	AA		YES	20/02/2010	6	STD	16/06/2016	15/06/2018
8	CC	YES		02/10/2012	3
9	CC		YES	08/06/2015	1	STD	16/06/2016	15/06/2018
10	AA			20/02/2000	16
11	BB			15/04/2000	16
12	CC			05/05/2017	0
13	BB	YES		09/12/2019	-3
14	CC			10/10/2010	5
15	CC	YES		10/10/2010	5
16	CC	YES		11/10/2010	5
17	CC			12/10/2010	5

TABLE_PERCENTAGE

REGION	PERCENTAGE
AA	50
BB	75
CC	50

RESULT

ID	S/N	REGION	FLAG_X	FLAG_Y	DATE_DEL	AGE (YEARS)	PROGRAM	BEGIN	END
1	1	AA	YES	YES	20/10/2010	5	STD	21/06/2016	20/06/2018
2	6	BB	YES	YES	01/01/2015	1	STD	21/06/2016	20/06/2018
3	14	CC		YES	10/10/2010	5	STD	21/06/2016	20/06/2018
4	16	CC	YES	YES	11/10/2010	5	STD	21/06/2016	20/06/2018
5	13	BB	YES	YES	09/12/2019	0	STD	09/04/2020	08/04/2022
6	3	BB	YES	YES	20/10/2008	7	STD	01/01/2016	31/12/2017
7	5	CC	YES	YES	20/12/2015	0	STD	16/06/2016	15/06/2018
8	7	AA		YES	20/02/2010	6	STD	16/06/2016	15/06/2018
9	9	CC		YES	08/06/2015	1	STD	16/06/2016	15/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!

[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]

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.