Group of excel files with same data, but not necessarily all fields in common

[stevelord]

Hi, I have these excel sheets that are being autogenerated such that if a field has no values in it, it will not appear on the sheet.  Otherwise, the fields are all the same.  In this case, the 'followup promised' field is the only one that seems to be populated for some sheets and not populated (and thus not appear) on others, and it is tripping the load when I try to load from *.xlsx with field not found errors.

I tried a concatenate load to see if that would help get around it, but it still trips.  Is there something I can do, besides loading the files with the field in one batch and without the field in another batch?  (Loading in separate batches would require me to check each file as they come in, and keep me from 100% automation,)  Here's my script:

EmailAgingTicketCount:

LOAD ApplyMap('ClientNameMap', F1) as Skill,

     [Followup Promised],

     [In Progress],

     Open,

     [Waiting on External],

     [Waiting on Internal],

     [Waiting on Tier 2],

     [Waiting on User],

     left(Right(Filename(),15),4) as FileYear,

     left(Right(Filename(),22),2) as FileDay,

     ApplyMap('MonthMap', left(Right(Filename(),19),3)) as FileMonth,

     ApplyMap('MonthMap', left(Right(Filename(),19),3))&'/'&left(Right(Filename(),22),2)&'/'&left(Right(Filename(),15),4) as FileDate,

     FileName() as FileName,

     'EmailAgingTicketCount' as DataType

FROM

[<filepath>\Email\Aging Ticket Count\QV_Aging_Ticket_Count_04-Apr-2014_19-22.xlsx]

(ooxml, embedded labels, table is Worksheet);

Concatenate LOAD ApplyMap('ClientNameMap', F1) as Skill,

     [Followup Promised],

     [In Progress],

     Open,

     [Waiting on External],

     [Waiting on Internal],

     [Waiting on Tier 2],

     [Waiting on User],

     left(Right(Filename(),15),4) as FileYear,

     left(Right(Filename(),22),2) as FileDay,

     ApplyMap('MonthMap', left(Right(Filename(),19),3)) as FileMonth,

     ApplyMap('MonthMap', left(Right(Filename(),19),3))&'/'&left(Right(Filename(),22),2)&'/'&left(Right(Filename(),15),4) as FileDate,

     FileName() as FileName,

     'EmailAgingTicketCount' as DataType

FROM

[<filepath>\Email\Aging Ticket Count\QV_Aging*.xlsx]

(ooxml, embedded labels, table is Worksheet) Where FileName()<>'Aging Ticket Count\QV_Aging_Ticket_Count_04-Apr-2014_19-22.xlsx';

[vadimtsushko · Accepted solution]

Hi Steve.

All load tasks should be automated

I would propose such an three step algo:

1. Load all xls files into resident table without any transformations, with * as field selector.
2. Check if resulting temp table has a field in question and store result of that test into variable
3. Finally load data from temp table applying all transformations, and conditionally loading null value or field value as fiel in question based on value of that that variable.

All code could look like:

EmailAgingTicketCountTmp:
LOAD *,
     FileName() as FileName FROM [<filepath>\Email\Aging Ticket Count\QV_Aging_Ticket_Count_04-Apr-2014_19-22.xlsx]
(ooxml, embedded labels, table is Worksheet);
Concatenate LOAD *,
     FileName() as FileName FROM FROM
[<filepath>\Email\Aging Ticket Count\QV_Aging*.xlsx]
(ooxml, embedded labels, table is Worksheet) Where FileName()<>'Aging Ticket Count\QV_Aging_Ticket_Count_04-Apr-2014_19-22.xlsx';


LET isFollowupPromised_Loaded = IsNull(FieldValueCount('Followup Promised'));


EmailAgingTicketCount:
NoConcatenate
LOAD ApplyMap('ClientNameMap', F1) as Skill,
     If($(isFollowupPromised_Loaded),[Followup Promised],Null()) as [Followup Promised],
     [In Progress],
     Open,
     [Waiting on External],
     [Waiting on Internal],
     [Waiting on Tier 2],
     [Waiting on User],
     left(Right(Filename,15),4) as FileYear,
     left(Right(Filename,22),2) as FileDay,
     ApplyMap('MonthMap', left(Right(Filename,19),3)) as FileMonth,
     ApplyMap('MonthMap', left(Right(Filename,19),3))&'/'&left(Right(Filename,22),2)&'/'&left(Right(Filename,15),4) as FileDate,
     FileName,
     'EmailAgingTicketCount' as DataType
RESIDENT EmailAgingTicketCountTmp;
DROP TABLE EmailAgingTicketCountTmp;

[stevelord]

Thanks, I didn't have time to test but as I was realizing another solution, I realized your solution was it.  I recognize the hack of loading one file, then concatenate loading all the rest to get around the differing fields and the subsequent let and if statement for the field in question look straight to me.  Thanks again!

PS> Boss said that field is unneeded, so that with 9pm on a Friday told me no more need to test.  If anyone comes back and says your answer is wrong, I can remove the correct answer credit, but it really does look straight to me.