topic Re: How to create a variable list from a table field in QlikView

How to create a variable list from a table field

Fri, 24 Jan 2014 12:31:36 GMT

Hi,

I'm trying to build a match-clause for a load script.

The current values are stored in a table called LatestProfileAssessments, field name is Assessment_Period_Sort.

How can i fill a variable vLatestPeriods to look like '201402','201401','201312','201311'

Thanks.

Re: How to create a variable list from a table field

Anonymous — Fri, 24 Jan 2014 12:47:49 GMT

Create a variable using below expression.

=Concat(Assessment_Period_Sort)

Re: How to create a variable list from a table field

Fri, 24 Jan 2014 12:54:51 GMT

Hi,

=chr(39) & concat(Fieldname, chr(39) & ',' & chr(39) ) & chr(39)

the concat is here with the separator option in order to create the ',' separator.

chr(39) is in front, and at the end to add a new quote

Fabrice

Re: How to create a variable list from a table field

Peter_Cammaert — Fri, 24 Jan 2014 13:02:06 GMT

Why not load all Assessment_Period_Sort values in a mapping table, and use applymap() to match values? Very fast.

Peter

Re: How to create a variable list from a table field

Sat, 25 Jan 2014 21:31:33 GMT

Currently I cannot follow any of the solutions, as what I read from the table field is null, although the field is filled. Strange. I tried to use Peek and concatenate commands, both ended up in an empty variable.

Perhaps I'm following a wrong approach in general, I try to load only the records from the 5 latest time periods. For that I create a sorted table that then contains these records, and then create a variable from it, but perhaps there is an easier solution.

Any ideas?

Re: How to create a variable list from a table field

Sun, 26 Jan 2014 12:50:53 GMT

Jens,

1) Load your time periods into a temp table

2) Sort your temp table (load into temp table 2 resident First Table order by date periods)

3) Usee peek, sth like

Mavar= peek(field of date, NoOfRows('Temp Table 2')-5, 'Temp Table2')

4) Load you file WHERE Date period >= $(MaVar)

Fabrice

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 07:31:31 GMT

Hi Fabrice,

I follow your suggestion, but I have the same issue as before. The variable doesn´t seem to work, as it is not added to the Variable overview and step 4 fails because of this.

Here's my code:

Sort_Tmp:

LOAD Distinct date#("Assessment_Period", 'YYYYMM') as "Assessment_Period_Date"

;

SQL SELECT "Assessment_Period"

FROM "Rep_Webdata_Prod".dbo.tblProfileAssessment

;

LatestProfileAssessments:

LOAD "Assessment_Period_Date",

"Assessment_Period_Date" as "Assessment_Period_Date_Sorted"

resident Sort_Tmp order by Assessment_Period_Date;

let vLimit= peek(Assessment_Period_Date_Sort, NoOfRows('LatestProfileAssessments')-5, 'LatestProfileAssessments');

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:00:14 GMT

Hi,

Hope this helps you

Create a variable and assign an expression as =Concat(GetFieldSelections(Assessment_Period_Sort, ','))

Thank you

Vardhan

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:06:38 GMT

Like this?

let vLimit = Concat(GetFieldSelections(Assessment_Period_Sort, ','));

Leads to a script error.

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:06:50 GMT

Jens,

It shoudl work.

Check your first table Sort_Temp. is it empty ? or sth in it ?

- Replace the double quoutes - the " - by single quoutes - '- or brackets - [] - around the field names.

- check your field in the SQl database. Is the format correct ? Can QV interprets that field as a correct date ?

Fabrice

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:19:46 GMT

- The table Sort_Tmp contains 180 rows.

- Done - not working, as the variable is still not created

- The field in the DB is of type String. That's why I try to convert to date during load - maybe there is an error?

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:22:43 GMT

Use Set instead of Let ..

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:24:59 GMT

OK. Data is retrieved from SQL database. But the dark hole comes after.

Peek syntax is: peek('field name with quote', number, 'table with quoute')

I think you forgot one quote in the first argument.

Mayi ask yoy to check ?

Fabrice

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:29:24 GMT

This won't work as it then doesn't calculate the right side, but instead writes the string as variable.

Re: How to create a variable list from a table field

Mon, 27 Jan 2014 08:30:32 GMT

That's it, now it works :-).

Thanks for your support and patience :-).