Change IN-DB Filter Expression via Checkbox Interface (Update Value with Formula)

[ThomasT]

Hi all, 

I have an IN-DB filter which I want to update via a checkbox to include/exclude certain values. The idea is that if a user ticks the checkbox the date in the filter tool changes to 2030 otherwise it will go to 2020.

The filter customer expression is currently "VALID_FROM" > TO_DATE('2030-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and I would like to change that date from 2030 to 2020 when a user ticks the checkbox. My attemt at the expression is following:

If [#1] = 'True' then '"VALID_FROM" > TO_DATE('2030-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')' else '"VALID_FROM" > TO_DATE('2000-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')' endif

I have used similar expressions previously with the detour tool eg If [#1] = 'True' then 'True' else 'False' endif but I'm at a loss how to manipulate a filter tool. Alteryx keeps saying my If statement is malformed...

[TimN · Accepted solution]

I think it's your quotes.  Try this

If [#1] then 'Date_Date' + " > TO_DATE('2030-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')" else 'DATE_DATE' + " >  TO_DATE('2000-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')" endif

Or arrange the quotes properly.

[TimN]

Replace 'Date_Date' with your field name, btw.

If [#1] then 'VALID_FROM' + " > TO_DATE('2030-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')" else 'VALID_FROM' + " >  TO_DATE('2000-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')" endif

[ThomasT]

Thanks @TimN it was the quotes. Since there is lots of single quotes inside the filter expression I just had to change around the single and double quotes to make it work. 

For reference, this is the correct way to write the IF statement

If [#1] = 'True' then "'VALID_FROM' > TO_DATE('2030-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')" else "'VALID_FROM' > TO_DATE('2000-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')" endif