All load tasks should be automated
I would propose such an three step algo:
- Load all xls files into resident table without any transformations, with * as field selector.
- Check if resulting temp table has a field in question and store result of that test into variable
- 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;
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.