Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Setting multiple Values as Fields

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:

2017-01-18 08-34-24.png

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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another example using the already proposed generic load:

QlikCommunity_Thread_246564_Pic1.JPG

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

View solution in original post

10 Replies
Not applicable
Author

HI ,

Use Generic Load

test:

Generic LOAD ID,

     Property,

     Value

FROM

q.xlsx

(ooxml, embedded labels, table is Blad2);

lironbaram
Partner - Master III
Partner - Master III

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
Not applicable
Author

Hi Nathalie,


find the attachment.


Regards,

Murali Mohan .T

MarcoWedel

Hi,

another example using the already proposed generic load:

QlikCommunity_Thread_246564_Pic1.JPG

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

Not applicable
Author

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

Not applicable
Author

Hi Marco

Absolutely. Plz see attached file.

MarcoWedel

Hej,

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

QlikCommunity_Thread_246564_Pic2.JPG

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

Not applicable
Author

Hi Marco.

Thanks a million!

Regards

//Nathalie