topic Re: Setting multiple Values as Fields in QlikView

Setting multiple Values as Fields

Wed, 18 Jan 2017 07:40:48 GMT

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

Re: Setting multiple Values as Fields

Wed, 18 Jan 2017 07:49:24 GMT

HI ,

Use Generic Load

test:

Generic LOAD ID,

Property,

Value

FROM

q.xlsx

(ooxml, embedded labels, table is Blad2);

Re: Setting multiple Values as Fields

lironbaram — Wed, 18 Jan 2017 07:54:43 GMT

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;

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

drop Table Tables;

Re: Setting multiple Values as Fields

Anonymous — Wed, 18 Jan 2017 11:54:30 GMT

Hi Nathalie,

find the attachment.

Regards,

Murali Mohan .T

Re: Setting multiple Values as Fields

MarcoWedel — Thu, 19 Jan 2017 01:30:38 GMT

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

Re: Setting multiple Values as Fields

Thu, 19 Jan 2017 06:36:10 GMT

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

Re: Setting multiple Values as Fields

MarcoWedel — Thu, 19 Jan 2017 22:57:38 GMT

‌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

Re: Setting multiple Values as Fields

Mon, 23 Jan 2017 05:38:05 GMT

Hi Marco

Absolutely. Plz see attached file.

Re: Setting multiple Values as Fields

MarcoWedel — Mon, 23 Jan 2017 21:44:26 GMT

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

Re: Setting multiple Values as Fields

Wed, 01 Feb 2017 12:24:24 GMT

Hi Marco.

Thanks a million!

Regards

//Nathalie

Re: Setting multiple Values as Fields

MarcoWedel — Wed, 01 Feb 2017 23:55:15 GMT

you're welcome

Marco