Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
1adam_haj
Contributor III
Contributor III

Load data from excel without text in rows

Hello i have excel document that is source of data for me but i have problem that qlik does not see all the data because of text in source document

Value 1Value 2Value 3
110100
220200
330300
Value 1Value 2Value 3
440

400

550500
660600

The qlik can see only the green values and do not see the data under the second value header. Is there a way how to load the data without the second header line in load script ? Some fuction to ignore the row in data load script or something.

Thanks in advance

1 Solution

Accepted Solutions
big_dreams
Creator III
Creator III

Not sure what are your looking for but try below

Temp:

Load *,if(isnum(Column1)=0 or isnum(Column2)=0,'String','Num') as Flag from tableName;

Noconcatenate

Final

Load * from Temp

where Flag='Num'

drop table Temp;

Logic is like

1: load all data from sheet flag on the basis of data in column

2: Filter out data from table created in 1 st step load only num data.

You can modify logic as per your requirement.

Regards,

View solution in original post

6 Replies
prma7799
Master III
Master III

Are you looking for this in Qlikview

Go to Edit Script >>Load that Excel >> Enable Transformation Step >>Select row which you don't want >> Delete Marked >>Next > finish.

girishsrini
Partner - Contributor III
Partner - Contributor III

Hi Adam,

If its in QlikSense, QlikSense picks complete data including second header as you have mentioned.

It considers all rows as data only. During load you can exclude it in where clause.

big_dreams
Creator III
Creator III

Not sure what are your looking for but try below

Temp:

Load *,if(isnum(Column1)=0 or isnum(Column2)=0,'String','Num') as Flag from tableName;

Noconcatenate

Final

Load * from Temp

where Flag='Num'

drop table Temp;

Logic is like

1: load all data from sheet flag on the basis of data in column

2: Filter out data from table created in 1 st step load only num data.

You can modify logic as per your requirement.

Regards,

girishsrini
Partner - Contributor III
Partner - Contributor III

Adam,

I am sure you will know the where clause. I checked it in QlikSense Desktop. Please check below.

1adam_haj
Contributor III
Contributor III
Author

Thank you very much, as you said I used Where clause with IsNum() funciton and it worked.

Chanty4u
MVP
MVP

Nice solution