Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

leni_balakrishn
Contributor III

To check the End of File

Hi,

Is there any way to check the end of file??

I have a requirement like, we have a list of folders and inside each folder a text file will be there whose name is same in all the folders.

So I need to take data from those text files to Qlikview. Now I can fetch data from the text files but I need to have a field or a value to identify the end of each file.

I will be using that filed or value in an another table to check whether It came from same file or from a different file.

Is there any way to do this??

Thanks in advance.

Regards,

Leni Balakrishnan

Tags (1)
18 Replies

Re: To check the End of File

I don't quite understand what you are trying to achieve. What does that file look like? Is the content of the file always the same in every directory?


talk is cheap, supply exceeds demand
leni_balakrishn
Contributor III

Re: To check the End of File

Let me try to explain in detail.

Example :

20120930 ->log.txt

20121001 -> log.txt

20121005 -> log.txt

The log.txt is a log file which will be created for each day. So the content in the log file will be different.

I am loading all these log.txt from different folders (20120930,20121001,20121005) to Qlikview.

So first it loads log.txt from 20120930 and then from 20121001 and then from 20121005 and so on.

Now I need an information like the details are taken from 20120930\log.txt file, 20121001\log.txt file and so on.

How to store this information.??

A new folder will be created for each date. So I need to store this information dynamically

Now I think this would have given you a picture on what i am trying to do.

Thanks,

Leni

Re: To check the End of File

Sorry, but I'm not understanding it quite yet. You have a number of directories, each of which holds a file named log.txt. The log files are created per date, so the number of directories will grow in time. You want to load each log.txt in qlikview. So you need a script that dynamically finds all the log.txt files and loads them. That's possible. Have you already achieved this?

I assume the contents of the log files will have the same format, i.e same number of columns (or maybe just a line of text that can be split into columns). Is that correct?

Next, you're talking about storing. What do you want to do? Load each log.txt and then store it as a qvd or excel file? Or do you want to store other information. Can you give some more details on what you want to do with the data once you've loaded it into qlikview?


talk is cheap, supply exceeds demand
leni_balakrishn
Contributor III

Re: To check the End of File

Yes the all the log files have same format and not the content.

I have dynamically loaded all the log files and loaded into Qlikview.

My need is to find from which log file the data are loaded.

Like for each date the data loaded would be different so once it load into Qlikview, will have all the informations in a single table. In tat I need to identify from which log.txt file the data is loaded, it is from 20120930\log.txt or 20121001\log.txt file and so on.

Thanks,

Leni Balakrishnan

Re: To check the End of File

Ah, ok. That should not be to hard. You can use filedir() to get the name of the directory and use that to fill an extra field.

Something like

load *,

filedir() as logdirectory

from ....


talk is cheap, supply exceeds demand
leni_balakrishn
Contributor III

Re: To check the End of File

Ya Now I am using like this only. But I need to know is there any other way.??

My actual requirement is

I am creating 2 fields StartTime and EndTime

In this EndTime = Time and StartTime = Previous(Time)

as I am loading all the files together once the end of first file comes next file will take its starttime as first file time. So its giving wrong data.

So before calculating the startTime I have a condition like whether the data from same file or different file

Whether we can achieve this using any other logic??

Thanks for your help 

Thanks,

Leni

Re: To check the End of File

You want to have all the records from the same file to have the same starttime and endtime?

If so, this might do the trick:

load

....

if(rowno()=1,Time,peek(StartTime) as StartTime,

if(Time>peek(EndTime),Time,peek(EndTime) as EndTime

from ...


talk is cheap, supply exceeds demand
leni_balakrishn
Contributor III

Re: To check the End of File

Could you please explain the above code??

Thanks...

Re: To check the End of File

if rowno()=1 checks if we are on the first record. If we are then we use Time to use as StartTime. If we are not on the first row we peek to the previous record and get StartTime. That way for all records StartTime will be the same as Time of the first record.

The second if statement you can ignore. That won't work. My brain tripped me .

I doubt you can do it in one pass. If you loop twice through the files you can do something like

StartEndTimes:

load

filepath() as logdir,

min(Time) as Startime,

max(Time) as Endtime

from logfile;

and later join StartEndTimes with the table with the logdata on logdir


talk is cheap, supply exceeds demand
Community Browser