topic Re: Saving one file multiple times in QlikView

Saving one file multiple times

don_qlikview — Fri, 31 Jan 2014 18:49:18 GMT

Hello Everyone,

I have a file that comes out 5 different times in the day and every time when it comes out, it replaces the previous file. For example File1.xlsx comes out at 11:00 AM then again it is replaced by File1.xlsx at 1:00 PM and so on. So, my question is how do I save these files as I have to do a subtraction of 1:00 PM – 11:00 AM and so on… Also I have a field in the file that is called “Generated time” and it gives the date and time in the format 1/31/2014 11:00:00 AM. Can anyone help me with this?

Thanks!!!

Re: Saving one file multiple times

Clever_Anjos — Fri, 31 Jan 2014 18:59:43 GMT

Which process is generating those files?

Re: Saving one file multiple times

don_qlikview — Fri, 31 Jan 2014 19:01:44 GMT

It's coming out of SAP and it seems they cannot change the file name or do anything!!!

Re: Saving one file multiple times

ashwanin — Fri, 31 Jan 2014 19:03:33 GMT

I think you have to do some modification in the Script /Macro from where this file is reloading. Check that what is the output format of that. Might be You have to change that

Re: Saving one file multiple times

Anonymous — Fri, 31 Jan 2014 19:10:28 GMT

Don

Why not load each SAP file into QlikView when they arrive and then store each one into a QVD named with suffix of date and hour derived from your “Generated time” field.

Then with another qvw you can load the QVD's you need and transform them as per your needs.

Best Regards, Bill

Re: Saving one file multiple times

Fri, 31 Jan 2014 19:49:58 GMT

Hi Don,

If you know the exact schedule time the file is being downloaded from SAP, then I suggest you to make a QVD file after it finished its process and naming this QVD file something like this:

Let TimeStamp = now();

SAP_Excel:

Load

From

[..\File1.xlsx]

(ooxml, explicit labels , header is 1 lines,table is [Table1]);

Store SAP_Excel into [..\qvdDestination\SAP_Excel_$(TimeStamp ).QVD];

Drop Table SAP_Excel ;

Re: Saving one file multiple times

ashwanin — Fri, 31 Jan 2014 20:26:03 GMT

Don, Suggestion of Bill sounds great. You can do it by this method.

Re: Saving one file multiple times

don_qlikview — Fri, 31 Jan 2014 20:38:36 GMT

Here is the sample data that I am getting

Generated Time	Name	ID	Sales
1/31/2014 10:20	abc	1	100
1/31/2014 10:20	afsd	2	200
1/31/2014 10:20	dfad	3	30
1/31/2014 10:20	asdfg	4	4748
1/31/2014 10:20	gwd	5	544
1/31/2014 10:20	age	6	99

So, the generated time changes every two hours, the next time it becomes 1/31/2014 12:20:00 PM, so if I store it in qvd how should I write the condition for saving it?

Re: Saving one file multiple times

don_qlikview — Fri, 31 Jan 2014 21:42:11 GMT

Can you give me an example of how I can do this.