Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikdash
Valued Contributor

How to Identify if first row is BLANK

I have a csv file that some times has 1st row blank and sometimes has it filled. how do I identify when 1st row in csv file is blank. can I use rowno().

Tags (2)
1 Solution

Accepted Solutions

Re: How to Identify if first row is BLANK

Hi Qlikdash,

vHeaderSize = If( Len(Trim(Peek('@1',0, 'Check')))>0,0,1)


Actually, we are storing the header size either 0 or 1 in the above variable for each csv file. The above script should load both the csv files along with file status. I did a test and it's working fine. I have attached the same for your understanding,

16 Replies

Re: How to Identify if first row is BLANK

What kind operation you want to do if you know first row is empty????

Yes you can use rowNo() it will return row number.

You can also try with first in load.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
MindaugasBacius
Valued Contributor III

Re: How to Identify if first row is BLANK

I guess you should simple use WHERE not(isnull(FieldName) clause in your loading state.

qlikdash
Valued Contributor

Re: How to Identify if first row is BLANK

empty header.PNG

where does not work because the first row before the header is empty. I have multiple files. some have headers from row 1. some have it from row 2. how to identify the two?

Partner
Partner

Re: How to Identify if first row is BLANK

I think it cannot be handled in qlikview because qlikview by default fetches the first row. I think you need to rectify and correct the format for excel file itself.

trdandamudi
Honored Contributor II

Re: How to Identify if first row is BLANK

Regardless of first row is blank or not blank the load will still work. For an example please take a look at the attached example where I loaded three excel files where two of them has a blank row and one which do not have a blank row. Hope this helps..

antoniotiman
Honored Contributor III

Re: How to Identify if first row is BLANK

Try like this

LOAD @1 as FIELD1,
@2 as FIELD2
FROM
[Cartel1.xls]
(biff, no labels, table is Foglio1$)
Where Len(Trim(@1)) > 0 and @1
<> 'FIELD1';

'FIELD1' is Label in Excel File

Re: How to Identify if first row is BLANK

I am not sure what Thirumala has suggested in the above qvw file. I am not able to open the file, since I'm using personal edition. You can try something like below,

vChkTable:

Load A

From

Excel.xlsx

(ooxml, no lables, table is sheet1);

Let vChk = Peek('A',0, 'vChkTable');

Drop Table vChkTable;

If  Len(Trim($(vChk) ))>0 Then

Do some action

Else

Do something

End If

This is just a concept for one file. You can easily adapt this code in a loop to load multiple files. There are plenty of loop code available in the forum. If you have any difficulties, let us know.

qlikdash
Valued Contributor

Re: How to Identify if first row is BLANK

Tamil, Thanks for replying


so here is more details:  I am loading all files at once (csv). excel files automatically deletes first blank row in QV. but with csv, there is an option of choosing header is 0 or 1 line as u can see below from the script.

Temp1:

LOAD [Field A],

    [Field B],

    [Field C],

    [Field D],

    FileName() as Filename

FROM

[..\Data Quality\*July28*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 0 lines);

trying to create a table that lists types of data issue. 1st Expn'July28_3' - if fieldD is null, bad or else good.

Here is the Real Question: My 2nd Expn 'Header Issue':- if a filename has 1st row empty(before header), i want a label Good, if it does not, 'Bad'. In the sample, 28_3 has a blank row. so header issue sud be good for this and bad for rest. how to make this possible.

data issues.PNG

[..\Data Quality\*July28*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 0 lines);

HINT: when header is 0 lines, it will not load if there is a blank row correct? same rule when header is 1 line. so how do I set it to accept both....may be??

Re: How to Identify if first row is BLANK

Hi qlikdash,

Got it. Here is the code,

Data:

Load '' as Dummy AutoGenerate 0;

Sub ScanFolder(Root)

     For Each FoundFile in Filelist( Root & '\*.' & FileExtension)

        Check:

        LOAD @1

        FROM

        [$(FoundFile)]

        (txt, codepage is 1252, no labels, delimiter is ',', msq);

    

        Let vHeaderSize = If( Len(Trim(Peek('@1',0, 'Check')))>0,0,1);

        Drop Table Check;

    

        Concatenate (Data)

        LOAD [Field A],

             [Field B],

             [Field C],

             [Field D],

             FileName()as Filename,

             If ('$(vHeaderSize)'=0, 'Good','Bad') as FileStatus

        FROM

        [$(FoundFile)]

        (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(vHeaderSize) lines);

      next FoundFile

End Sub

Call ScanFolder('..\Data Quality')

DROP Field Dummy;