topic Re: Variable in Load Scrip - Field Name in QlikView

Variable in Load Scrip - Field Name

gerhardl — Fri, 14 Feb 2014 12:44:03 GMT

Hi,

I'm trying to use a variable value (a date) as part of a field name in my load script.

Basically I want the date of the file (which is part of the file name) to be part of the field name.

So I am loading from a text file with a name like this: Daily_AccountExtract_DUNNS_13-FEB-2014_20140214051009.txt

I can create a field which gets the date of the file like this:

DATE(DATE#( mid(filebasename(),28,11), 'DD-MMM-YY')) as File_Date

I would like to rename the [Account balance] field to [Balance as at File_Date], but cannot seem to get it right.

I have tried something like this:

[Account balance] as "$(='Balance as at ' & DATE(DATE#( mid(filebasename(),28,11), 'DD-MMM-YY'))" ,

I also tried:

LET vDate = DATE(DATE#( mid(filebasename(),28,11), 'DD-MMM-YY'))

LOAD [Account balance] as "$(='Balance ' & vDate)" ,

Is this possible - FYI, my end goal is to be able to load multiple files with different dates, and by this method separate balance fields will be loaded for each account.

Re: Variable in Load Scrip - Field Name

alexandros17 — Fri, 14 Feb 2014 12:48:52 GMT

if your field is myField and your variable is $(myVar)

try:

load

...

myField as 'myField'$(myVar)

Re: Variable in Load Scrip - Field Name

kumarnatarajan — Fri, 14 Feb 2014 12:53:23 GMT

Hi,

Try below one.

Ex:

Load [Account balance] as [Balance $(vDate)],...ect

Re: Re: Variable in Load Scrip - Field Name

gerhardl — Fri, 14 Feb 2014 12:58:01 GMT

this doesn't work - please see image attached.

Re: Re: Variable in Load Scrip - Field Name

gerhardl — Fri, 14 Feb 2014 13:01:23 GMT

Hi,

This doesn't work - the variable "vDate" is just empty.

I don't think I can set the variable at the top of the load script because at that point it doesn't know yet what file I'm going to load?

Re: Variable in Load Scrip - Field Name

jonathandienst — Fri, 14 Feb 2014 13:04:44 GMT

You can certainly do this, but permit me to ask why? This will lead to a suboptimal model that could be awkward to work with, so unless there is a specific reason you are doing this, you would almost certainly be better off storing the date as an attribute in the fact table, rather than creating multiple value fields.

However you won't be able to do this in a single pass with FileBaseName as FileBaseName is only defined in a read statement and you wont be able to create a variable as used in the examples above (which are correct).

To load dynamically in this case, I would try For Each vFile in FileList(' <some folder/file mask> ') and parse vFile for create the field name suffix variable before loading the file and then use suffix variable in the method described in the earlier posts.

HTH

Jonathan

Re: Variable in Load Scrip - Field Name

rwunderlich — Fri, 14 Feb 2014 13:05:37 GMT

No single quotes. Just square brackets exactly as Kumar has suggested.

-Rob

Re: Variable in Load Scrip - Field Name

kumarnatarajan — Fri, 14 Feb 2014 13:07:11 GMT

Hi,

It's working fine. Lead the below script.

LET vdate=MakeDate(2012,1,1);

Quarter:
LOAD

Year,Quarter as [Balance $(vdate)]

INLINE

[
Year, Quarter
2012, Q1
2012, Q2
2012, Q3
2012, Q4
]

;

Re: Variable in Load Scrip - Field Name

maxgro — Fri, 14 Feb 2014 13:15:03 GMT

if I understand your question

SCRIPT

Directory;

LOAD

distinct mid(filebasename(),28,11) as File_Date

FROM

[Daily_AccountExtract_DUNNS_13-FEB-2014_20140214051009.txt]

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

LET vFile = peek('File_Date');

LOAD

@1 as [AccountBalance at $(vFile)]

FROM

[Daily_AccountExtract_DUNNS_13-FEB-2014_20140214051009.txt]

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

RESULT

AccountBalance at 13-FEB-2014
1
2
3
4
5
6
7
8
9

Re: Re: Variable in Load Scrip - Field Name

gerhardl — Fri, 14 Feb 2014 13:20:42 GMT

Hi Jonathan,

Thanks for the detailed reply.

To be honest I am just "playing around" with a few ideas. Since you ask, here is why...

My ultimate goal is a reduction and roll rate report which users currently do in Excel. I used balance as an example above, but the roll rate checks are firstly done using a field called [CD Status], which can have values from 1 to 9, and indicates how far a customer is in arrears on his account, CD 1 being current and CD 9 being 8 months in arrears.

Our billing month is from the 11th of the month until the 10th of the next. Roll rate would then be the % of customers who were in CD1 and didn't pay and therefore "rolled" into CD2 (and so on for each CD Status). The end result, from which charts are created, would look like the attached excel images (I've blacked out company names).

Those reports are created from summary values (i.e. the total count per CD status) and they are not 100% accurate (long store why), so I want to start creating them from detail, i.e. by loading Daily Account Snaps and counting accounts that moved from 1 CD status to the next.

To do this I (might) need to keep history of what each individual account's Balance and CD Status was on different dates.

Like I said - just playing around for now to figure out my options.