topic Re: How to Identify if first row is BLANK in QlikView

How to Identify if first row is BLANK

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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().

Re: How to Identify if first row is BLANK

PrashantSangle — Thu, 28 Jul 2016 05:44:19 GMT

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

Re: How to Identify if first row is BLANK

MindaugasBacius — Thu, 28 Jul 2016 05:47:46 GMT

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

Re: How to Identify if first row is BLANK

Anonymous — Thu, 28 Jul 2016 13:50:36 GMT

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?

Re: How to Identify if first row is BLANK

saimahasan — Thu, 28 Jul 2016 13:59:40 GMT

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.

Re: How to Identify if first row is BLANK

trdandamudi — Thu, 28 Jul 2016 14:11:10 GMT

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..

Re: How to Identify if first row is BLANK

antoniotiman — Thu, 28 Jul 2016 15:09:45 GMT

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

tamilarasu — Thu, 28 Jul 2016 15:19:22 GMT

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.

Re: How to Identify if first row is BLANK

Anonymous — Fri, 29 Jul 2016 01:35:23 GMT

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

tamilarasu — Fri, 29 Jul 2016 02:27:35 GMT

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;

Re: How to Identify if first row is BLANK

Anonymous — Fri, 29 Jul 2016 03:06:41 GMT

Tamil,

Thank you for your prompt reply. I will test it and get back.

Regards.

Re: How to Identify if first row is BLANK

Anonymous — Fri, 29 Jul 2016 05:33:46 GMT

Tamil, I tried your soln. it is calculating $(vHeaderSize) as having 0 value and thus loading the file without a blank row only.

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

in the code above it seems it is resulting only one value based on "if it is >0". can we make it consider less than 0 as well?

how can we pass both 'data types' so that they become avail on the front end to distinguish which file has a blank row and which one does not.

Thanks again. I really appreciate.

Re: How to Identify if first row is BLANK

tamilarasu — Fri, 29 Jul 2016 05:55:01 GMT

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,

Re: How to Identify if first row is BLANK

antoniotiman — Fri, 29 Jul 2016 07:38:19 GMT

Hi,

see attachment

Regards,

Antonio

Re: How to Identify if first row is BLANK

Anonymous — Fri, 29 Jul 2016 16:16:40 GMT

Tamil,

Thanks again for sending that. I tested yours and it works but it did not work in my qvw. it might be the folder structure I don't knw. I will update.

Re: How to Identify if first row is BLANK

tamilarasu — Sat, 30 Jul 2016 01:14:08 GMT

Hi Bro,

You can use debug mode to check whether the files are taken properly. Especially the line "FoundFile In FileList". You can also check log file to identify the path and file names.

Just remove the ..\Data Quality from the below line and paste your absolute path and try again.

Call ScanFolder('..\Data Quality')

Re: How to Identify if first row is BLANK

Anonymous — Fri, 05 Aug 2016 17:53:38 GMT

Tamil, Thank you so much for your help. It took me a while because I was investigating why it was not working. Your code works in the sample but did not work in my actual csv data files. One reason was the cell below @1 had len =1 from a white space. this was being treated as having a populated row. Below is the code that worked for me. I have the major changes in Blue highlight

In summary, I am first forcing the csv files with 0 header lines which also gets 'bad' file status. if there is an error it will load the remaining files which have 1 header lines and will have 'good' as header status. need to notify one thing which is I am not using $(vHeaderSize) functionality, which would have been more dynamic.

Data:

Load '' as Dummy AutoGenerate 0;

sub ScanFolder(Root)

for each FoundFile in filelist( 'C:\Users\DataIssue\*.csv')

vChkTable:

LOAD @1

FROM

[$(FoundFile)]

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

Let vHeaderSize = Peek('@1',0, 'vChkTable');

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

Drop Table vChkTable;

Concatenate (Data)

LOAD C1,

C2,

C3,

FileName()as Filename,

'Bad' as FileStatus

FROM

[$(FoundFile)]

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

if ScriptError=11 then

Concatenate (Data)

LOAD C1,

C2,

C3,

FileName()as Filename,

'Good' as FileStatus

FROM

[$(FoundFile)]

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

end if

next FoundFile

SET vPath='C:\Users\DataIssue\*.csv';

End Sub

Call ScanFolder('$(vPath)')

DROP Field Dummy;