Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Expertise Needed ... stalwar1 lironbaram
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