topic Re: Load inline date based on file name in QlikView

Load inline date based on file name

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Is there a way to load an inline date based on a file name? Every day I expect to save a new excel file into my source folder and run a qlikview script to concatenate each file together. However, I want to differentiate each file load by the as of date of the data. For example, yesterday I saved an excel source data file as "Source Data (9-20-2016).xlsx", and today I saved another file as "Source Data (9-21-2016).xlsx". When I run my script, I'd like for the data pertaining to the 9-20-2016 to be identified as such, and the same applies to the 9-21-2016 data.

Is this possible?

For example, in my load script:

LOAD
Name,
[Open Balance]
FROM

(ooxml, embedded labels, header is 4 lines, table is [$(Sheet1)]);

Re: Load inline date based on file name

neelamsaroha157 — Wed, 21 Sep 2016 16:20:08 GMT

Try this

For each vFileName in Filelist ('Folder Path\*Demo*.csv')

[$(vFileName)]:

NoConcatenate

Load *,

'$(vFileName)' as FileName

From [Path](txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Load inline date based on file name

sunny_talwar — Wed, 21 Sep 2016 16:20:49 GMT

Is this what you are trying to get?

Qlikview Extract Date from FileName() by RFB 148 - YouTube

Re: Load inline date based on file name

Wed, 21 Sep 2016 17:00:49 GMT

Wow guys....your answers were awesome! When I run my run my script, I'm able to date the load now, however I'm running into a bunch of $Syn now. How can I concatenate the data loads so that I can avoid these $Syn?

Here's my actual load script:

ARDetail:
LOAD
*,
ApplyMap('TransTypeOverride',[Document Number], [Temp - Trans Code 2]) as [Trans Code],
ApplyMap('FiscalWeek',[Due Date],'PRIOR') as [Fiscal Week];
LOAD
*,
if([CB Code]<>Null() and [Transaction Type]<>'Credit Memo','CB',if([Transaction Type]='Journal' and [AR Specialist]<>'NONE','CM',[Temp - Trans Code 1])) as [Temp - Trans Code 2],
date([Transaction Date]+[Repayment Days],'MM/DD/YY') as [Due Date];
LOAD
*,
ApplyMap('CustomerNameLong',[Customer #],'Loading Error - Customer Name (Long)') as [Customer Name (Long)],
ApplyMap('CustomerNameShort',[Customer #],'Loading Error - Customer Name (Short)') as [Customer Name (Short)],
ApplyMap('CombinedName',[Customer #],'Loading Error - Combined Name') as [Combined Name],
ApplyMap('ARSpecialist',[Customer #],'Loading Error - AR Specialist') as [AR Specialist],
ApplyMap('RepaymentDays',[Repayment Terms],'0') as [Repayment Days],
ApplyMap('Domestic',[Customer #],'Y') as [BBC],
ApplyMap('Government',[Customer #],'No') as [Government],
ApplyMap('AccountTypeCorrections',[Customer #],[Temp - Account Type]) as [Account Type],
ApplyMap('TransType',[Transaction Type],Null()) as [Temp - Trans Code 1],
ApplyMap('CBDescription',[CB Code],Null()) as [CB Description],
ApplyMap('CBTypeSummary',[CB Code],Null()) as [CB Type Summary],
ApplyMap('CBOpsvNonOps',[CB Code],Null()) as [CB Ops V. Non-Ops],
ApplyMap('CBReserveCategory',[CB Code],Null()) as [CB Reserve Category];
LOAD
*,
Subfield([Temp - Customer # 1],' ',1) as [Customer #];
LOAD
Name,
if(WildMatch(Name,'*:*'),subfield(Name,': ',-1),Name) as [Temp - Customer # 1],
[Account Type] as [Temp - Account Type],
[Customer Type],
[Sales Code],
[Document Number],
[Transaction Type],
date([Transaction Date],'MM/DD/YY') as [Transaction Date],
[Repayment Terms],
date([Due Date],'MM/DD/YY') as [Temp - Due Date],
[CB Code],
[P.O. No.],
[Open Balance],
Date(Date#(LEFT(RIGHT(FileName(),13),8),'YYYYMMDD'),'MM/DD/YYYY') as FileDate
FROM
[\\\Accounting\Accounts Receivable\~QlikView Reports~\AR Detail\AR Detail*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [$(Sheet1)]);

How do you concatenate each consecutive load when I have so many preceding loads?