topic Re: compose date from day field and folder name in QlikView

compose date from day field and folder name

Fri, 11 Nov 2016 08:02:02 GMT

Hi there, I`m new in QlikView;

I load data from a Paradox database, splitted in many files into different folders.

All the transactions files are splitted in months. Each month is represented by one folder name like that

c:\DATA\2016_01

c:\DATA\2016_02

c:\DATA\2016_03

The load script looks like that and it works well

SET Dir=`c:\DATA\*`;

OLEDB CONNECT32 TO....

FOR each Dir in dirlist ('$(Dir)')

LOAD SupplierID,

Quantity,

day,

etc...;

SQL *

FROM $(Dir)\fact.db;

NEXT Dir

The problem is that in fact.db I don't have a real field for date but only a day column so I should create a new date field in load from day field and each folder name. I suppose I need to apply a mask at load from '$(Dir)' but do not know how to do that

Anyone can help me?

Re: compose date from day field and folder name

pokassov — Fri, 11 Nov 2016 08:25:13 GMT

Hello!

Something like that:

SET Dir=`c:\DATA\*`;

OLEDB CONNECT32 TO....

FOR each Dir in dirlist ('$(Dir)')

LET vCnt = SubStringCount(Dir,'\');

LET vYearMonth = SubField(Dir,'\',Cnt-1);

LET vYear = SubField(vYearMonth,'_',1);

LET vMonth = SubField(vYearMonth,'_',2);

LOAD SupplierID,

Quantity,

day,

MakeDate($(vYear),$(vMonth),day) As Date,

etc...;

SQL *

FROM $(Dir)\fact.db;

NEXT Dir

Re: compose date from day field and folder name

Chanty4u — Fri, 11 Nov 2016 08:31:42 GMT

you can try this also

load *,

Date(Floor(MonthEnd(Date#(keepchar(FileBaseName(),'1234567890'),'YYYYMM'))),'DD-MM-YYYY')

as Date1,

FROM

Re: compose date from day field and folder name

Fri, 11 Nov 2016 09:33:30 GMT

if i try that i get "field not found"
in debug show that:

ScriptErrorDetails	<NULL>
ScriptErrorList	General Error File Not Found File Not Found
vMonth	<NULL>
vYear	<NULL>
vYearMonth	<NULL>
Cnt	<NULL>
vCnt	4

Re: compose date from day field and folder name

Fri, 11 Nov 2016 09:38:37 GMT

works if i try smoething like that

Date(Floor(MonthEnd(Date#(keepchar(FileBaseName('$(Dir)'),'1234567890'),'YYYYMM'))),'DD-MM-YYYY')

as Date1;

but Date 1 is empty

should i use somehow also the day field?

Re: compose date from day field and folder name

Chanty4u — Fri, 11 Nov 2016 10:39:42 GMT

try like this

load *,

Date(Floor(MonthEnd(Date#(keepchar(FileBaseName(),'1234567890'),'YYYY_MM'))),'DD-MM-YYYY')

as Date1,

FROM

Re: compose date from day field and folder name

pokassov — Fri, 11 Nov 2016 10:55:02 GMT

please change these lines:

LET vYearMonth = SubField(Dir,'\',vCnt+1);

LET vYear = SubField(vYearMonth,'_',1);

LET vMonth = SubField(vYearMonth,'_',2);

Re: compose date from day field and folder name

maxgro — Fri, 11 Nov 2016 12:49:12 GMT

SET Dir=`c:\DATA\*`;

OLEDB CONNECT32 TO....

FOR each Dir in dirlist ('$(Dir)')

LOAD

MonthEnd(Date#(replace(Right(( '$(Dir)' ), 7), '_', ''), 'YYYYMM')) as NewField,

SupplierID,

Quantity,

day,

etc...;

SQL *

FROM $(Dir)\fact.db;

NEXT Dir