compose date from day field and folder name

[#1]

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?

[pokassov]

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

[Chanty4u]

you can try this also

load *,

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

      as Date1,

FROM

[#4]

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

[#5]

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?

[Chanty4u]

try like this

load *,

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

      as Date1,

FROM

[pokassov]

please change these lines:

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

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

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

[maxgro]

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