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