topic Help with variable in script in QlikView

Help with variable in script

hopkinsc — Mon, 15 Oct 2012 10:08:28 GMT

Hi all,

I am trying to create a variable in the script from another variable.

first i create this ..

LET vCurrentYearFiscalStartDate = '=DATE(FirstDayCurrentFiscalYear)';

Then i want to create another variable using the above variable.

LET

vItemCount_Listings =
'COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">=$(vCurrentYearFiscalStartDate)"}
>}
[ld_Item_Description])';

The above didnt work, so after a bit of research i tried the following which also didnt work.

LET

vItemCount_Listings =
'COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">='&chr(36)&'(=vCurrentYearFiscalStartDate)"}
>}
[ld_Item_Description])';

Does anyone have any ideas please?

Re: Help with variable in script

swuehl — Mon, 15 Oct 2012 10:16:27 GMT

Maybe like this?

LET

vItemCount_Listings =

'COUNT({$<

[it_target_type]={"Listings"},

[ld_Dist]={"4"},

[ld_Outlet_Check_Date]={">='&chr(36)&'($(vCurrentYearFiscalStartDate))"}

[ld_Item_Description])';

Re: Help with variable in script

hopkinsc — Mon, 15 Oct 2012 10:32:32 GMT

Hi Stefan,

that brings back a zero value.

If i change it to use a date instead of variable then i get a value of 139.

LET

vItemCount_Listings =

'COUNT({$<

[it_target_type]={"Listings"},

[ld_Dist]={"4"},

[ld_Outlet_Check_Date]={">=01/07/2012 00:00:00"}

[ld_Item_Description])';

But i want this to work using a variable instead of hardcoding the date/

Re: Help with variable in script

swuehl — Mon, 15 Oct 2012 10:38:29 GMT

If I run the script, I get this variable definition back:

COUNT({$<

[it_target_type]={"Listings"},

[ld_Dist]={"4"},

[ld_Outlet_Check_Date]={">=$(=DATE(FirstDayCurrentFiscalYear))"}

[ld_Item_Description])

I think this is what you intend to get back, right?

If you say, you get a value of 139 back (resp. zero), I assume you mean you get this returned when using the variable in an e.g. text box expression, right?

I can imagine some issues with the set modifier format, 01/07/2012 00:00:00 is a timestamp format, while your standard DATE format is probably missing the time string.

Re: Help with variable in script

hopkinsc — Mon, 15 Oct 2012 10:44:02 GMT

Hi Stefan,

Yes you arfe exactly right. I am getting the 139 in a text box using the variable name. what would be the easiest way of getting around the timestamp issues. we dont have the option to remove the time as this is needed.

Re: Help with variable in script

swuehl — Mon, 15 Oct 2012 10:48:10 GMT

Just match the format of your initial variable with the field format, maybe like

LET vCurrentYearFiscalStartDate = '=Timestamp(FirstDayCurrentFiscalYear,'DD/MM/YYYY hh:mm:ss')';

Re: Help with variable in script

hopkinsc — Mon, 15 Oct 2012 11:17:01 GMT

Hi Stefan,

Thanks

I got it working by just using the Timestamp function. I had script errors when i used the ,'DD/MM/YYYY hh:mm:ss' at the end.

Many thanks for your help

Chris