Calling a Variable in SQL SELECT

[alutz54]

I have a variable that I created that gets the previous 6 fiscal periods and puts them in 'MMM_FY-YY' format. When the variable is created, it ends up looking like this in the debugger: 

vFiscalPeriods = "'MAY_FY-23','APR_FY-23', 'MAR_FY-23', 'FEB_FY-23', 'JAN_FY-23', 'DEC_FY-23'"

When I try to call that is a future SQL SELECT WHERE clause like this:

SQL SELECT

...

FROM dbo.table

WHERE gl.FiscalPeriod IN $(vFiscalPeriods)

I get an error saying that qlik sense can't find the table columns I am trying to reference. When I hardcode it as: 

WHERE gl.FiscalPeriod IN ('MAY_FY-23' , 'APR_FY-23' , 'MAR_FY-23' , 'FEB_FY-23' , 'JAN_FY-23' , 'DEC_FY-23')

it will then work and has no problem finding  the table columns being referenced. Any advice on how to get my variable to work?

[stevedark]

Hi @alutz54 

When setting the variable you want to use a SET statement and you can drop the double quotes around the outside:

SET vFiscalPeriods = 'MAY_FY-23','APR_FY-23', 'MAR_FY-23', 'FEB_FY-23', 'JAN_FY-23', 'DEC_FY-23';

Where you come to use the variable with dollar sign expansion it is like copying and pasting the text into the expression, so you need to add the brackets around the list of months:

WHERE gl.FiscalPeriod IN ($(vFiscalPeriods))

If the script breaks, looking at what appears in the load script dialog should help, as this will show what is in the variable expanded.

Looking at how the debugger shows variable content, it adds extra double quotes when it shows the variable, so it may just be the missing brackets that are catching you out.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/

[alutz54]

Hi @stevedark 

Would the SET function still work if my variable is not set to a string? I have a whole for loop that determines the current and previous fiscal periods and formats them which leads up to my final vFiscalPeriods variable. My impression was that if my variable is not equal to a string literal, I wouldn't want to use SET.

[stevedark]

Hi @alutz54 

The set statement only works if you are putting in a fixed string, typed directly into the load script. This would be useful for testing what you are trying, but not for building the string automatically.

Given what you see in the variable debugger, I think it may just be the missing brackets that are your problem. I had typed the set stuff ahead of fully getting my ahead around your question.

Steve

[marcus_sommer]

Instead of using SET you may use LET which does evaluate the right part before assigning the result to the variable - and with it you could create dynamic results. If it's very beautiful to concat multiple statements like:

chr(39) & replace(date(monthstart(today(), -3), 'MMM_##-YY'), '##', 'FY') & chr(39)

to create an appropriate string? But it's possible.

Beside this such a listing could be also generic created with a load by using a string-aggregation which result is afterwards picked per peek() and stored in a variable. Also I suggest to adjust the format to a number like YYYYMM because the field-name contains already the fiscal information and it's not necessary to add them to each field-value - and (pure) numbers could be compared with >= <= and/or adding any offset and you would avoid all formatting-troubles and the need to quote each call and saving performance and ... and you may change the where-clause to such a  >= <= comparing ...