Load data from excel without text in rows

[1adam_haj]

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 1	Value 2	Value 3
1	10	100
2	20	200
3	30	300
Value 1	Value 2	Value 3
4	40	400
5	50	500
6	60	600

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

[prma7799]

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]

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 · Accepted solution]

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]

Adam,

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

[1adam_haj]

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

[Chanty4u]

Nice solution