How to create a variable list from a table field

[#1]

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.

[Anonymous]

Create a variable using below expression.

=Concat(Assessment_Period_Sort)

[#3]

  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

[Peter_Cammaert]

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

Peter

[#5]

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?

[#6 · Accepted solution]

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

[#7]

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');

[#8]

Hi,

Hope this helps you

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

Thank you

Vardhan

[#9]

Like this?

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

Leads to a script error.

[#10]

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