Auto upload daily .txt file

[gerhardl]

Hi,

I'm new to QlikView and would like some help with the following.

Our system creates reports every day with the day-end batch run. One of these reports is a Daily Account Extract, which is a '~' delimited text file listing all accounts on our book with about 130 different fields.

I've created a QV file with an overview of all accounts, some nice charts and graphs, etc.

What I want to do is automatically update the file with the new daily extract whenever I open the QV file. At the moment I do the following:

• Edit script - then select table files, browse to the latest extract, rename twho fields that have the same name in the text file, and apply certain settings (like the delimiter type, header size, etc.)
• Then I type 'replace' in front of the 'load' statement so it replaces the current data with the new data for the day.

What I would like to do is open the file, and somehow have the above done automatically.

The daily extracts are generated and sent to a new folder daily, i.e {L:\Einstein Reports\2011\042011\06042011}, and the next day it will be in {L:\Einstein Reports\2011\042011\07042011}.

Is this possible to do?

[gerhardl]

Also, when I "replace" the file and click on reload, it will load every single daily file, which takes forever. If I do the daily, then in a few weeks it will take an hour to reload all of the daily files.

I want it to just load the last file; it whould be the only file in the script editor, but all the document settings and sheet items should remain.

[gauravkhare]

Hi Gerhardl,

this is an easy job...You just need to declare a time stamp and perform incremental load. In this way only the new data will be added. Just go through some tutorial of incremental load which can be found in QlikView Professional course. There are three types of incremental load.

Or you can send me your sample data and i will be making script for you.

[gerhardl]

Hi Gaurav,

I am pretty clueless to be honest. Would very much appreciate if you could help me with the script.

Please let me know what data you need from me.

• The account extract will be in a different folder every day.
• The name of the extract will be "Daily_AccountExtract_A_06-APR-2011_06042011041229.txt" (the last 14 characters being the time and date it was generated).
• Our time-stamp and date format also seem to differ from the defaults in QV.
• The following settings need to be applied:
  • Delimter type ~
  • Header size 1
  • Embedded labels
  • Quoting - None

[jonathandienst · Accepted solution]

gerardl

You have two problems to solve to automate the process, so lets start by breaking this down to check that I have understood you correctly.

• You have some issues with a duplicate field name. I assume that the text file contains field headers. If you convert the load statement from LOAD * to:

LOAD field1,
 field2,
 field2 AS field2a,
 .....
 field130
FROM ....

That should take care of the duplicate field name (obviously replace my sample field names with the correct ones). I know you have a lot of field names. What I do is copy the header line to excel, split the string on the delimiters (Text to Columns) and transpose the split string (Paste Special | Transpose) and then paste the results back into Qlikview.

• You need to only load the latest file. From your post it looks like the folder path is:

L:\Einstein Reports\YYYY\MMYYYY\DD\MMYYYY

Assuming the daily file is stamped with yesterday's date, a variable expression like this wil generate the path:

Let vYesterday = Today() - 1;
Let vPath = 'L:\Einstein Reports\' & Date(vYesterday, 'YYYY') & '\' & Date(vYesterday, 'MMYYYY') & '\' & Date(vYesterday, 'DDMMYYYY') ;

Then you can use vPath in the FROM clause like this:

LOAD
...
FROM $(vPath)\TEXTFILE.txt;<ul><li>Finally, to automate the load, use the QV server is you have the server, or use the Windows task scheduler to launch the reload from the command line:</li></ul>
"c:\program files\qlikview\qv.exe" /r MODELNAME.qvw
Set the scheduler to trigger the job daily between the time that the text file is dropped and the time you want to use the model. That way, the model can be reloaded automagically and ready for use.

Hope that helps
Jonathan</body>

[jonathandienst]

To convert the date, use the following (assuming your file has dates in DDMMYYYY format:

LOAD
....
Date(Date#(DATEFIELD, 'DDMMYYYY')) AS DATEFIELD,
....

This will convert the date in DATEFIELD from DDMMYYYY format to your default.

Jonathan

[gerhardl]

Hi Jonathan,

Thank you very much for your detailed response. I have absolutely no experience with coding of any kind, so I am still a bit confused as to where exactly the variable expressions should go. I also can't really understand how the time stamp and the file name is known, as this will always differ depending on how long the day-end batch run takes.

Would you be so kind as to look at my current script and add the variable expressions to it?

At the moment I am copying the daily extracts to a local folder and loading it from there (FYI). Also, our date format, of which there are many fields, are as follows: 07-mar-11 and certain date fields will also include time stamps, e.g. 02-APR-2011 10:15:29~06

Here is my script:

SET

ThousandSep=','

;

SET

DecimalSep='.'

;

SET

MoneyThousandSep=','

;

SET

MoneyDecimalSep='.'

;

SET

MoneyFormat='R #,##0.00;R-#,##0.00'

;

SET

TimeFormat='hh:mm:ss TT'

;

SET

DateFormat='YYYY/MM/DD'

;

SET

TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT'

;

SET

MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

;

SET

DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

;

Directory;

LOAD

[Product Name]

,

[ID number]

,

[Account no]

,

[Card no]

,

[Account open date]

,

[Account status code]

,

[Charge off Status]

,

[Account status change date]

,

[Previous status]

,

[Store no]

,

[Block type code1]

,

[Block type code2]

,

[Credit limit]

,

[Account balance]

,

[OTB (excl. oversell)]

,

[Total due]

,

[Total past due]

,

[Recency Delinquency Status]

,

[Cycle due status]

,

[CD0 value]

,

[CD1 value]

,

[CD2 value]

,

[CD3 value]

,

[CD4 value]

,

[CD5 value]

,

[CD6 value]

,

[CD7 value]

,

[CD8 value]

,

[CD9 value]

,

[Date of last payment]

,

[Value of last payment]

,

[Number of payments in cycle]

,

[Value of payments in cycle]

,

[Date of first purchase]

,

[Value of first purchase]

,

[Date of last purchase]

,

[Value of last purchase]

,

[Number of purchases in cycle]

,

[Value of purchases in cycle]

,

[Perm to increase limit]

,

[Perm to sms]

,

[Perm to email]

,

[Perm to telemark]

,

[Postal code]

,

[Work phone]

,

[Home phone]

,

[Cell phone]

,

[Title code]

,

[First name]

,

[Last name]

,

[Address line]

as [Address line 1]

,

[Address line]

as [Address line 2]

,

City

,

Province

,

[Dtorder flag]

,

[Bank for debit order]

,

[Bank code for debit order]

,

[Bank account for debit order]

,

[Date of charge off]

,

[OTB (incl. oversell)]

,

[Credit utilization]

,

[Days before activation]

,

[Ethnic group]

,

Gender

,

Age

,

[Payment profile month01]

,

[Payment profile month02]

,

[Payment profile month03]

,

[Payment profile month04]

,

[Payment profile month05]

,

[Payment profile month06]

,

[Payment profile month07]

,

[Payment profile month08]

,

[Payment profile month09]

,

[Payment profile month10]

,

[Payment profile month11]

,

[Payment profile month12]

,

[Payment profile month13]

,

[Payment profile month14]

,

[Payment profile month15]

,

[Payment profile month16]

,

[Payment profile month17]

,

[Payment profile month18]

,

[Payment profile month19]

,

[Payment profile month20]

,

[Payment profile month21]

,

[Payment profile month22]

,

[Payment profile month23]

,

[Payment profile month24]

,

[RG status at appl time]

,

[Interest rate current]

,

[Appl no]

,

[Comms language code]

,

[A+ insurance added]

,

[Date A+ added]

,

[date A+ cancelled]

,

[No of return mails]

,

[Wo reason code]

,

[Company Code]

,

[Date of birth]

,

[Language code]

,

[Statement indicator]

,

[Email address]

,

Employer

,

Occupation

,

[Spouse name]

,

[Spouse last name]

,

[Spouse home phone]

,

[Reference first name]

,

[Reference last name]

,

[Reference home phone]

,

[Campaign Reason]

,

[Reference No]

,

[Champion Challenge No]

,

[Offered Limit]

,

[Final Limit]

FROM

[Account Extracts\Daily_AccountExtract_A_06-APR-2011_07042011015627.txt]

(

txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)

;

[jonathandienst]

Gerhard

Please repost your code. Copy the code from Qlikview into a text editor (notepad will do). And then copy it from there to the forum, This will prevent it from spacing out like it has. If you do that, I can look at the code and make some suggestions

Regards

Jonathan