Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.