Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

1adam_haj
New 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

Tags (2)
1 Solution

Accepted Solutions
big_dreams
Contributor III

Re: Load data from excel without text in rows

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,

6 Replies
prma7799
Honored Contributor III

Re: Load data from excel without text in rows

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
New Contributor III

Re: Load data from excel without text in rows

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
Contributor III

Re: Load data from excel without text in rows

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
New Contributor III

Re: Load data from excel without text in rows

Adam,

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

1adam_haj
New Contributor III

Re: Load data from excel without text in rows

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

Chanty4u
Esteemed Contributor III

Re: Load data from excel without text in rows

Nice solution

Community Browser