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

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

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

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

shekhar_analyti — Sun, 26 Mar 2017 10:05:24 GMT

Expertise Needed ... stalwar1 lironbaram‌

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

hector_munoz — Sun, 26 Mar 2017 10:20:20 GMT

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;

I attach a sample. Hope it serves...

Regards,