Revising Excel format for loading into QlikView

[triciagdaly]

Referencing the attached spreadsheet which shows the actual spreadsheet received versus the desired format for loading into QlikView - just wanted to ask if it is possible to reformat.  One issue is the building location and the service type are in the same column.

Thank you.

[Gysbert_Wassenaar]

Something like this:

LOAD 
  if(not alt(previous([Days]),0), previous(Serv.Type),peek(Building)) as Building,
  Serv.Type, 
     [Prev. Read Date], 
     [Present Read Date], 
     Days, 
     [Prev. Rdg], 
     [Pres. Rdg], 
     Usage, 
     UOM, 
     [Usage Chgs]
FROM
[comm121954.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
where [Days];

See attached qvw.

[MayilVahanan]

Hi

Try like this

Load * where Match(Location, 'East Bldg', 'South Bldg','Grounds');

LOAD   if([Days] > 0, if(Isnull(previous(Days)), previous(Serv.Type), Peek('Location'))) as Location,  

Serv.Type,

[Prev. Read Date],

[Present Read Date],

Days,

[Prev. Rdg],

[Pres. Rdg],

Usage,

UOM,

[Usage Chgs]

FROM

Excel_import.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where Days > 0;

[triciagdaly]

Can you please explain how the below is interpreted using alt, previous and peek functions ?  

if (not alt(previous([Days]),0), previous(Serv.Type),peek(Building) as Building

[Gysbert_Wassenaar]

For the first row ther is no previous value, so previous(Days) would return null. The alt is used to change that to 0. That way it possible to to evaluate previous(Days) as a boolean: 0 is false, other number is true. If true then use the Serv.Type value of the previous record (from the source table) as value for Building, if false then use the last value of Building (from the target table).

Alt( MyField, MyValue): if value of MyField is null, use MyValue, otherwise use value of MyField

Previous(MyField): get the value of MyField from the previous record of the source table

Peek(MyField): get the value of MyField from the previous record of the target table