topic Revising Excel format for loading into QlikView in QlikView

Revising Excel format for loading into QlikView

triciagdaly — Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Revising Excel format for loading into QlikView

Gysbert_Wassenaar — Fri, 13 Jun 2014 14:56:26 GMT

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.

Re: Revising Excel format for loading into QlikView

MayilVahanan — Fri, 13 Jun 2014 15:17:10 GMT

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;

Re: Revising Excel format for loading into QlikView

triciagdaly — Fri, 13 Jun 2014 17:21:34 GMT

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

Re: Revising Excel format for loading into QlikView

Gysbert_Wassenaar — Fri, 13 Jun 2014 17:28:35 GMT

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