Loading CrossTable Empty Rows

[ogiheyn]

Hi everyone,

I'm trying to load a lab data from a customized table to qliksense. I'm trying to use CrossTable function here since it seems logical to me. Below is the screenshot of the data and my code as follows;

Temp:
CrossTable("Stat or Routine?", Data, 6)
LOAD
'$(vFacilityCode)' as [Facility Code],
Date($(vDate)) as Date,
'$(vFacilityCode)'&'-'&Num(Floor($(vDate)))&'-'&$(i) as _Facility_Code_Date_Key,
If( Len(Trim("B")), "B", Peek(B) ) as Department,
C as Section,
D as Stat,
"E" as Routine,
F as Stat1,
G as Routine1,
H as Stat2,
"I" as Routine2,
J as [%]

FROM [$(file)]
(ooxml, no labels, header is 2 lines, table is [Lab KPI - IT]) where Len(Trim("I")) > 0;

As a result I got the Department Rows empty but I just need them to be filled with the previous above (ED and Ipt for the case). Since it is not loading Departments for empty rows I cannot generate data for "Chemistry" dimension because it has null at the moment.

I want to create a table like this and filter based on the Routine2 and Stat2;

But I'm getting the data like this;

Any help will be very appreciated.