Create a list from a Table

[MagnusRydberg1]

Jag have a table(FuelType) med with different fuel types,  I want to create a list (Fueltypelist) with the different fuel types seperated with '|'.

Have tried this but it doesn't work

Validfueltype:

LOAD

    ID as v_FuelTypeID,

    Name as v_FuelTypeName

    FROM [lib://QlikDataQVD/MyC/MYCQVDSTORE/LSP/FuelType.qvd]

   (qvd);

Let LoopNumber=1;

For LoopNumber= 1 to NoofRows('Validfueltype')

LET Fueltypelist=Peek('v_FuelTypeName', LoopNumber-1,Validfueltype)+ '|'+Fueltypelist;

Next;

[JandreKillianRIC]

Hi @MagnusRydberg1 

Validfueltype:
LOAD

    ID as v_FuelTypeID,

    Name as v_FuelTypeName

    FROM [lib://QlikDataQVD/MyC/MYCQVDSTORE/LSP/FuelType.qvd]

   (qvd);
   
List:   
Load 
	Concat(Distinct v_FuelTypeName, '|') as List
Resident Validfueltype;

[MagnusRydberg1]

Thank a lot! It worked! 

But I also want the list to start and end with the ' character

I tried this but it didn't work (See Fueltypelist2 below):

// Valid Fuel type
Validfueltype:
LOAD
ID as v_FuelTypeID,
Name as v_FuelTypeName
FROM [lib://QlikDataQVD/MyC/MYCQVDSTORE/LSP/FuelType.qvd]
(qvd);

Fueltypelist1:
Load
Concat(Distinct v_FuelTypeName, '|') as Fueltypelist1
Resident Validfueltype;

chr(39) + Fueltypelist1 + chr(39 as Fueltypelist2;

[ramchalla]

Hi Magnus,

Please try this

Fueltypelist1:
Load
Concat(chr(39)& Distinct v_FuelTypeName&chr(39), '|') as Fueltypelist1
Resident Validfueltype;

[JandreKillianRIC]

Hi @MagnusRydberg1 

List:

Load
Concat(Distinct chr(39)&v_FuelTypeName&chr(39), '|') as List
Resident Validfueltype;

This will give you 'value1'|'value2' etc etc.

I assume this for a variable input that you want to use in the front end... If so, there is a "better" way. Let me know

Regards Jandre

[MagnusRydberg1]

Hi Jandre,

Thanks for your help. Yes it for use in the front end. I want the list to be:

'value1|value2|value3' etc. 

Best regards

// Magnus

[JandreKillianRIC]

Hi @MagnusRydberg1 

Exactly what I thought. Hahah

For the back end you can do it like this

Load

Chr(39)& Concat(Distinct v_FuelTypeName, '|')&chr(39) as List

Resident Validfueltype;

But on the front end you can create another variable or just in the dynamic fx box of where you would put the List field (created above) enter this

={1} Chr(39)& Concat(Distinct v_FuelTypeName, '|')&chr(39)

It would be the same result and you dont need to perform a reload. 

Let me know

Regards Jandre

[MagnusRydberg1]

It seems that the list should be OK now but the list doesn't show up in the front end...:

What am I doing wrong in the front end?

Thanks again

// Magnus

[JandreKillianRIC]

Hi @MagnusRydberg1 

Just use it like this 

=Fueltypelist1

That should work.

Regards Jandre 

[MagnusRydberg1]

Sorry but =Fueltypelist1 it shows:

But if I Use this direct in frontend unde f(x) it works! 🙂

={1} Concat(Distinct v_FuelTypeName, '|')

Best regards

// Magnus

But if I