Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
hope this helps
regards
Marco
HI ,
Use Generic Load
test:
Generic LOAD ID,
Property,
Value
FROM
q.xlsx
(ooxml, embedded labels, table is Blad2);
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;
Hi Nathalie,
find the attachment.
Regards,
Murali Mohan .T
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
hope this helps
regards
Marco
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
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
Hi Marco
Absolutely. Plz see attached file.
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
Hi Marco.
Thanks a million!
Regards
//Nathalie