topic Re: Load importing unwanted columns QLik sense in App Development

Load importing unwanted columns QLik sense

h2bi — Fri, 19 Jan 2024 08:07:00 GMT

Hello, i am trying to load a table, but QLik is loading unwanted empty columns, in the sheet we constantly add new column names with new data so i cannot just unselect those empty columns, i am looking for something that can only load the columns with names and data dynamically

My sheet:

The load problem:

table:
LOAD
"Date",
"45207",
"452071",
"452072",
"45210",
"45211",
"45216",
"45223",
F9,
F10,
F11,
F12,
F13,
F14,
F15
FROM [lib://tables/table.xlsx]
(ooxml, embedded labels, table is Sheet1);

Note: i cannot touch the excel file

Re: Load importing unwanted columns QLik sense

Rohan — Fri, 19 Jan 2024 10:11:24 GMT

Hi @h2bi ,

Try This :

T:
Load * inline [
Ta,Tb,Tc,Td
A,1,,
B,2,,
C,4,
D,,1,];

//Replace inline with your excel file location : FROM [lib://tables/table.xlsx]
//(ooxml, embedded labels, table is Sheet1);

for i = 1 to NoOfFields('T')

Let j=FieldName($(i),'T');

Temp:
Load distinct
'$(j)' as Fieldname
Resident T where len(trim($(j)))<>0;

next i;

Final:
Load
Concat(distinct Fieldname,',') as Final_List
Resident Temp;

drop table Temp;

Let v=peek('Final_List',0,'Final');

Trace '$(v)';

drop table Final;

NoConcatenate
TableA:
Load
$(v)
Resident T;

drop table T;

Exit script;

As per the code Td column won't load in my front end. Please refer attached image :

Let me know if it worked for you.

Regards,

Rohan.

Re: Load importing unwanted columns QLik sense

marcus_sommer — Tue, 23 Jan 2024 13:15:15 GMT

The columns are included from the wizard because they are be used - maybe just because that there is further any content. Deleting the content doesn't reset the used-flagging else only deleting entire rows/columns would reset this information. Therefore communicating with the Excel owner may remove this issue without any extra load-step.

Beside this the data-source is a crosstable which is probably later transformed into a normal data-structure and there is NULL automatically exclude and EMPTY values could be removed with a simple where-clause.