Setting multiple Values as Fields

[#1]

Hi again, guys!

I am trying transform a table I have and I am, again, lost in how to do this in a quick and dynamic way.

The problem is illustrated by the attached image:

I have got this to work statically, but if the Property Values change in any way,, my app will not follow the changes.

Is there a FOR loop or some smart if statement that pops to anyone's mind?

Thank you for all help you can provide!

//Nathalie

[#2]

HI ,

Use Generic Load

test:

Generic LOAD ID,

     Property,

     Value

FROM

q.xlsx

(ooxml, embedded labels, table is Blad2);

[lironbaram]

hi

there is a built in function to do exactly what you need

it call genric load

you can use the script below

i also attached a demo app

Data:

generic LOAD

  ID,

    Property,

    Value

FROM

(ooxml, embedded labels, header is 1 lines, table is Blad1);

let vTables= NoofTables(); // finding number of tables

let vDataTableName = TableName(0);////finding the name of first table

RENAME Table $(vDataTableName) to Data;//rename first table

//////////////////////connecting all tables back to one table //////////////////////////

////////////////creating a table with all table names //////////////////////////////////////////

for i =1 to NoofTables()-1

Tables:

load TableName($(i)) as TableName ,$(i) as RN AutoGenerate 1;

NEXT

set errormode = 0 ;

for i=1 to NoofTables()-1

////////////////////////////loop the tables and joinion them to the Data table ///////////////////////

let vTableName =  FieldValue('TableName', $(i));

join (Data)

load * resident  $(vTableName) ;

drop table $(vTableName);

next

drop Table Tables;

[Anonymous]

Hi Nathalie,

find the attachment.

Regards,

Murali Mohan .T

[MarcoWedel · Accepted solution]

Hi,

another example using the already proposed generic load:

tabIn:
Generic
LOAD ID,
    Property,
    Value 
FROM [https://community.qlik.com/servlet/JiveServlet/download/1195158-261586/q.xlsx] (ooxml, embedded labels, table is Blad1, filters(Remove(Row, Pos(Top, 1))));

tabOut:
LOAD '' as ID AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF '$(vTable)' like 'tabIn.*' THEN
  JOIN (tabOut) LOAD * RESIDENT $(vTable);
  DROP TABLE $(vTable);
  ENDIF
NEXT i

see also:

Use cases for Generic Load | Qlikview Cookbook

and

Qlik Design Blog

hope this helps

regards

Marco

[#6]

Thanks, Marco!

That worked like a charm!

Follow-up question: My only issue is that the actual values (not the example ones) that are supposed to become field names contain multiple special chars, which disturb your fine script. Is there a way of taking the entire Value?

For now I have used the Replace() function but it is not as pretty. 😃

Have a great day

//Nathalie

[MarcoWedel]

‌HI,

glad you liked it.

Can you post some samples including the same special characters like your real data?

I'd like to test with those.

thanks

have a nice day too.

Marco

[#8]

Hi Marco

Absolutely. Plz see attached file.

[MarcoWedel]

Hej,

adding some square brackets seems to work with your source table also:

tabIn:
Generic
LOAD RecNo() as ID,
    Property,
    Value
FROM [https://community.qlik.com/servlet/JiveServlet/download/1198007-262189/Example%20Prop%20Value.xlsx] (ooxml, embedded labels, table is Blad1);

tabOut:
LOAD '' as ID AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF '$(vTable)' like 'tabIn.*' THEN
  JOIN (tabOut) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

hope this helps

regards

Marco

[#10]

Hi Marco.

Thanks a million!

Regards

//Nathalie