How to create data model for below pattern of Excel data where data increases in different tab day wise ?

[shekhar_analyti]

Hi All ,

How to create Data model for attached data .

In excel sheet named (Jan-Data) , number of Tab increases day wise . For example on Day one of Month January . I am getting Data in bellow pattern in Tab 1 , on Day 2 at file location  old file gets replaced with new sheet having same name but additional tab Day2 gets added with data in similar pattern , it goes on like this till 31 days (when we will have 31 tabs) . But in Month of February at same location new excel sheets gets added with name (Feb-Data) and the tab count start increasing day by day .

Note : Jan-Data excel sheet remains at location .

How to handle above data and scenarios ?

Thanks & Regards

Shekar

[shekhar_analyti]

Expertise Needed  ... stalwar1  lironbaram‌

[hector_munoz · Accepted solution]

Hi Shekhar:

Try the following code:

LET vsInitialDate = Num('01/01/2017');

LET vsEndDate = Num(Today() - 1);

LET vsNumberOfIterations = $(vsEndDate) - $(vsInitialDate) + 1;

FOR vsCounter = 1 TO vsNumberOfIterations

  LET vsIterationDate = Date($(vsInitialDate) + $(vsCounter) - 1);

  LET vsIterationMonth = Month('$(vsIterationDate)');

  LET vsIterationDay = Num(Day('$(vsIterationDate)'));

  TABLE1:

  LOAD  [ComLTRy Name],

      [ComLTRy ID]

  FROM  $(vsIterationMonth)-Data.xlsx (ooxml, embedded labels, table is Day$(vsIterationDay))

  WHERE [ComLTRy Name] <> 'ComLTRy Name' AND

      Len([ComLTRy Name]) > 0;

  TABLE1:

  LOAD  [Vehilce Reg Number],

      [ComLTRy ID]

  FROM  $(vsIterationMonth)-Data.xlsx (ooxml, embedded labels, table is Day$(vsIterationDay))

  WHERE [Vehilce Reg Number] <> 'Vehilce Reg Number' AND

      Len([Vehilce Reg Number]) > 0;

  TABLE2:

  LOAD  [ComLTRy Name],

      [Vehicel ID]

  FROM  $(vsIterationMonth)-Data.xlsx (ooxml, embedded labels, table is Day$(vsIterationDay))

  WHERE [ComLTRy Name] <> 'ComLTRy Name' AND

      Len([ComLTRy Name]) > 0;

  TABLE3:

  LOAD  [Driver Name],

      [Driver ID Type],

      [Driver ID]

  FROM  $(vsIterationMonth)-Data.xlsx (ooxml, embedded labels, table is Day$(vsIterationDay))

  WHERE [Driver Name] <> 'Driver Name' AND

      Len([Driver Name]) > 0;

  TABLE4:

  LOAD  [Device Number],

      [Device ID]

  FROM  $(vsIterationMonth)-Data.xlsx (ooxml, embedded labels, table is Day$(vsIterationDay))

  WHERE [Device Number] <> 'Device Number' AND

      Len([Device Number]) > 0;

  TABLE5:

  LOAD  [ComLTRy ID1],

      [Vehicel ID1],

      [Device ID1],

      [Start Date],

      [End Date]

  FROM  $(vsIterationMonth)-Data.xlsx (ooxml, embedded labels, table is Day$(vsIterationDay))

  WHERE [ComLTRy ID1] <> 'ComLTRy ID1' AND

      Len([ComLTRy ID1]) > 0;

NEXT

I attach a sample. Hope it serves...

Regards,

H