topic Re: help combining variables in QlikView

help combining variables

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have the following variables..

vCounter = Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)

vPeriod_1 = Only({<Counter = {$(=vCounter-1)}>} nd_FISCAL_PERIOD)

as you can see, vPeriod_1 uses vCounter within it.

i need to combine the above 2 variables, the vCounter expression needs to replace the vCounter part of the vPeriod_1 variable expression.

then the i want to replace the vPeriod_1 part of the below expression with the combined expression from the above.

='("LV Snapshot FY$(=Right(CurrentFiscalYear,2))*" & (*$(vPeriod_1)))'

i cant get this to work, can anyone help please?

i have attached an example qvw.

thanks

Re: help combining variables

swuehl — Thu, 24 Nov 2016 21:49:46 GMT

Maybe

='("LV Snapshot FY$(=Right(CurrentFiscalYear,2))*" & (*$(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-1)}>} nd_FISCAL_PERIOD)))'

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 22:07:24 GMT

Hi Stefan,

thanks again! that works in QV (i changed the button to use that expression instead of the variable) but again it doesnt work in NP! AAARRRRRGGGGHHHHHH!!!!!

this is so frustrating!

Re: help combining variables

swuehl — Thu, 24 Nov 2016 22:10:23 GMT

Does it work if you hard code the search string in NP?

("LV Snapshot FY17*" & (*201704)

Just to check that the search approach does work out.

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 22:18:23 GMT

ok so the above doesnt work, it only works if the actual string is hardcoded

i.e.

LV Snapshot FY17 201704

so the problem is getting NP to evaluate the expression and apply the result.

Re: help combining variables

swuehl — Thu, 24 Nov 2016 22:23:41 GMT

Sorry, I missed a bracket:

("LV Snapshot FY17*" & (*201704))

("LV Snapshot FY17*" & "*201704")

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 22:28:30 GMT

i had already noticed that and corrected it in my test. didnt work.

within a text box, the expression...

='("LV Snapshot FY$(=Right(CurrentFiscalYear,2))*" & (*$(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-1)}>} nd_FISCAL_PERIOD))))'

gives the result of..

("LV Snapshot FY17*" & (*201704))

but this isnt a value in the Dataset field which i think is why its not making the selection.

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 22:33:40 GMT

i have changed the expression slightly to..

='LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-1)}>} nd_FISCAL_PERIOD))'

which removed some double quotes and some brackets, this in a text box matches one of the values in the Dataset field and also works in NP.

i dont know if this will have any other affect though so i will add in the other 2 periods and see what happens

Re: help combining variables

swuehl — Thu, 24 Nov 2016 22:34:01 GMT

Where are you using this search string in the context of NP?

Above is a compound search string, which should work in selection -select in field triggers, for example.

Compound Search - demystified

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 22:36:45 GMT

in NP i am creating a field filter on the dataset field and ticking the 'evaluate' check box (i have also tried without ticking the check box too.

Re: help combining variables

swuehl — Thu, 24 Nov 2016 22:50:43 GMT

Sorry, I don't know NPrinting good enough.

Maybe try to specifically address your issue to

Qlik NPrinting

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 22:52:05 GMT

last question i promise!

i now need to add in the additional 2 periods (201703 and 201702) which are from the other 2 variables you did.

for this to work in NP i would need the result to be comma separated, (i think) so the 3 values can be selected the same time.

is it possible to add these to the following expression?

='LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-1)}>} nd_FISCAL_PERIOD))'

Re: help combining variables

swuehl — Thu, 24 Nov 2016 23:02:45 GMT

Maybe like

='LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-1)}>} nd_FISCAL_PERIOD)),LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-2)}>} nd_FISCAL_PERIOD)),LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-3)}>} nd_FISCAL_PERIOD))'

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 23:06:38 GMT

that works in a text box, but how would i set it up in an action on the button to make the 3 selections on the Dataset field?

Re: help combining variables

swuehl — Thu, 24 Nov 2016 23:10:46 GMT

Don't know how NPrinting works, but in QV, you can use a compound search like

("value1"|"value2"|"value3")

Re: help combining variables

hopkinsc — Thu, 24 Nov 2016 23:21:47 GMT

thanks for all your help

i have changed the expression to the following..

='("LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-1)}>} nd_FISCAL_PERIOD))"|"LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-2)}>} nd_FISCAL_PERIOD))"|"LV Snapshot FY$(=Right(CurrentFiscalYear,2)) $(=Only({<Counter = {$(=Only({<nd_FISCAL_PERIOD = {$(=Only(CurrentFiscalPeriod))}>}Counter)-3)}>} nd_FISCAL_PERIOD))")'

which works in QV but i can't get it to work in NP.

i have asked Stephen Jasonowski at Qlik for support.

thanks again!