Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
h2bi
Partner - Contributor III
Partner - Contributor III

Load importing unwanted columns QLik sense

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:

h2bi_0-1705651455549.png

The load problem:

h2bi_1-1705651488020.png

 

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

Labels (3)
2 Replies
Rohan
Specialist
Specialist

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 :

Rohan_0-1705659047455.png

 

Let me know if it worked for you.

Regards,

Rohan.

 

marcus_sommer

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.