how to run a for loop for each row to populate a column based on some condition

[noviceneil]

Hi,

       Could you please suggest how to run a for loop for each row in a table having 4 columns.

I want to run a loop for each row to populate decision column based on some conditions I perform on date1 and date2 values.

The algorith is like, 

for each row in TABLE A

         if  current_date between date1 and date2 then decision ='A'

else if      date2 <  current_date then decision='B'

else decision='C'

end

My TABLE A already exists like below( with a blank decision column initially):

TABLE A:

ID    date1              date2          decision

11      010120        120522     

22       010120       100421

33      010521        120521  

44      010120        301225

Could you please help as I am new to QlikSense.

[JuanGerardo]

Hi @noviceneil , I think the best way is to load a new table from the existing one:

NewTable:

Load *,

  If(Today() >= date1 And Today() <= date2, 'A',

      If(date2 <  Today(), 'B', 'C')

   ) AS Decision

Resident [TABLE A];

Drop Table [TABLE A];  // Not needed anymore, so I drop it

JG

[jwjackso]

A Preloading script ( in bold) might work.  You can stack load statements and they are evaluated bottom to top.

TABLEA:

load ID

        ,date1

        ,date2

        ,If(date1 >= Today() and Today() =< date2,'A', If(date2 < Today(),'B','C')) as decision;

load * inline [

ID,date1,date2,decision

11,010120,120522,

22,010120,100421,

33,010521,120521

44,010120,301225

]

[noviceneil]

@jwjackso  thanks for your reply. Actually I am restricted to use the inline function as the actual tables includes much more rows and columns( apologies if I needed to mention it). is there any alternative?

[noviceneil]

@JuanGerardo  Thanks you for your reply. It's almost working but only a little glitch , could you please help?

BOND_ID     purchase_date          maturity_date           contribution          BOND_Type          prev_curr_flag
1234                    12/03/2020                 31/12/2025                  5000                          EXT                             1
1234                     12/03/2020                 31/12/2025                 4800                          INT                              0
1112                     13/03/2017                  31/02/2020               66000                         EXT                             0
1114                     13/03/2021                  31/02/2022               70000                         EXT                             1

Sciript:

SET 'CURRENT_BNSDT' =15/03/2021;
SET 'PREVIOUS_BNSDT' =01/12/2020'';

INPUT:

LOAD
BOND_ID,
purchase_date,
maturity_date,
contribution,
BOND_Type,
prev_curr_flag
FROM [lib://AttachedFiles/TEST_QS_INPUT.xlsx]
(ooxml, embedded labels, table is INPUT);

SCRIPT:

SET 'CURRENT_BNSDT' =15/03/2021;
SET 'PREVIOUS_BNSDT' =01/12/2020;

SET vCURRENT_DATE= $(CURRENT_BNSDT); //'15/03/2021';
SET vPREVIOUS_DATE= $(PREVIOUS_BNSDT); //'01/12/2020';

INPUT:

LOAD
BOND_ID,
purchase_date,
maturity_date,
contribution,
BOND_Type,
prev_curr_flag
FROM [lib://AttachedFiles/TEST_QS_INPUT.xlsx]
(ooxml, embedded labels, table is INPUT);

OUTPUT_TABLE:
Load *,
IF( ([BOND_ID]=PREVIOUS([BOND_ID]) ) and ([BOND_Type]<> PREVIOUS([BOND_Type])) , 'TYPE CHANGED',
IF ([BOND_ID] <>PREVIOUS([BOND_ID]) and maturity_date < $(CURRENT_BNSDT),'CONTRACT MATURED',
IF ( [BOND_ID]<>PREVIOUS([BOND_ID]) and purchase_date > $(PREVIOUS_BNSDT) and maturity_date > $(CURRENT_BNSDT),'CONTRACT purchased')) ) as REASON

RESIDENT INPUT;
DROP TABLE INPUT;

I am getting the OUTPUT like following:

BOND_ID     purchase_date    maturity_date    contribution   BOND_Type     prev_curr_flag         REASON
1234                 12/03/2020            31/12/2025                  5000                EXT                       1                   CONTRACT PURCHASED
1234                 12/03/2020            31/12/2025                  4800                INT                        0                  TYPE CHANGED
1112                 13/03/2017            31/02/2020               66000                EXT                        0                   CONTRACT PURCHASED
1114                 13/03/2021            31/02/2022               70000                EXT                        1                   CONTRACT PURCHASED

where as the expected result is,

I am getting the OUTPUT like following:

BOND_ID     purchase_date    maturity_date    contribution   BOND_Type     prev_curr_flag         REASON
1234                 12/03/2020            31/12/2025                  5000                EXT                       1                  
1234                 12/03/2020            31/12/2025                  4800                INT                        0                      TYPE CHANGED
1112                 13/03/2017            31/02/2020               66000                EXT                        0                   CONTRACT MATURED
1114                 13/03/2021            31/02/2022               70000                EXT                        1                   CONTRACT PURCHASED

Could someone please provide an idea to get out of this.

[JuanGerardo]

Hi, I have a couple of suggestions for you to try:

Keep an eye on the quotes, not in variable names, but in values:

SET CURRENT_BNSDT = '15/03/2021';
SET PREVIOUS_BNSDT = '01/12/2020';

When comparing those dates, use Date function to be sure the format is correctly interpreted, and use the quotes properly:

... and maturity_date < Date('$(CURRENT_BNSDT)', 'DD/MM/YYYY') ...

JG