Excel file with multiple header rows and commas for decimals

[mikegrattan]

I'm trying to work with a file that comes from an industrial computer.  Before I can import into my Qlik application, I need to figure out how to handle the multiple header rows and data that uses a comma for a decimal point.  (The data comes from a Priva system if anyone's familiar).

I've attached a sample of the data.  What I'd like to do is use Row 1 for the field names.  I'm not sure what do to with Row 2,as it seems to contain the computer or sensor location for that particular column of data.  Row 3 is a unit of measure, so possibly that could be concatenated after the field name from Row 1 (if that's possible).  I will then need to also add a field name for column 1, row 1 because that is currently blank, but it should be something like ReadingDateTime.  More than likely I will split that field into separate date and time fields so I can have a TimeOfDay field for comparitive analysis.

Lastly, I will need to convert the commas in the data to decimal points.

If anyone can provide suggestion on how to handle any of these challenges, please feel free to comment.

File attached.

[stascher]

Here's a way to do it without the units:

Table:

LOAD

    F1 as ReadingDateTime,

    num(replace("meas grh temp",',','.')) as "meas grh temp",

    num(replace("meas grh temp1",',','.')) as "meas grh temp1",

    num(replace("outside temp",',','.')) as "outside temp",

    num(replace("meas CO2",',','.')) as "meas CO2",

    num(replace("meas north",',','.')) as "meas north",

    num(replace("meas south",',','.')) as "meas south",

    num(replace("meas north1",',','.')) as "meas north1",

    num(replace("meas south1",',','.')) as "meas south1",

    num(replace("meas grh temp2",',','.')) as "meas grh temp2",

    num(replace("meas RH",',','.')) as "meas RH",

    num(replace("radiation sum",',','.')) as "radiation sum",

    num(replace("meas curtain 1",',','.')) as "meas curtain 1"

FROM [lib://TopLevelScripts/develop\01_QVS\PrivaTest.xlsx]

(ooxml, embedded labels, table is [annual data]) where RecNo()>2;

Of course you'll have to customize the input path for your own environment.

[marcus_sommer · Accepted solution]

Your data is a crosstable. In most cases it's very helpful to transform it into a normal table-structure with The Crosstable Load‌. How to handle multiple header is described here: multi_header_pivot_import.qvw.

After this you could easily convert and format the numbers with num(num#()) and handling the various date-formats with alt(Format1, Format2, ...) and split your timestamp with date(floor(YourTimestamp)) as Date respectively time(frac(YourTimestamp)).

- Marcus

[mikegrattan]

Thank you Steven; I think this will be helpful.

[mikegrattan]

Thank you Marcus.  I have used the Crosstable Load before, once or twice, so I am a bit familiar with it.  For some reason this data just didn't jump out at me as an obvious crosstable.  I'll take a look at it today, as well as your other suggestions.

[mikegrattan]

I'm in Qlik Sense, but luckily also have one license for Qlik View.  I was able to open that QVW file and go through the script and adapt it for my file.  It worked great.  Thanks for posting that.

Edit and update:

I have been waiting on this since Priva was supposed to be looking into another alternative by giving us direct access to their SQL Server tables.  That hasn't happened yet, so I tried running my modified script (which worked in Qlikview) in Qlik Sense and it does not work.  There are syntax errors that I cannot resolve.  I tried getting help on the syntax via another community post, but the suggestion didn't work.

The errors appear to be occuring in the Levels and CT areas of the script, while trying to interpret the variables vType and vVDims.  Those variables are declared earlier in the script as follows:

SET vType = 'ooxml'; //  'ooxml'

LET vHDims = FieldValueCount('HFieldName');

LET vVDims = FieldValueCount('VFieldName');

However, the syntax errors seem to be related to these variables:

Any ideas for getting this to work in Qlik Sense marcus_sommer‌?

Thanks.

[marcus_sommer]

On the first view it looked fine. Difficult to say what could be go wrong in Sense. A t first I would check with TRACE or the debugger what the used variables really contain to see if there is anything different as expected.

Beside this I would remove the comment by vType or using as /* comment */ instead of // - because it's a potential risk: Well-commented variables. Be careful!

In regard to vVDims it should work - maybe there is some bug in this. An alternatively could be to set the whole crosstable-statement as variable like:

let vCrosstable = 'Crosstable(ValCol, Amount, ' & $(vVDims) & ')';

and then:

$(vCrosstable)

load ...

- Marcus

[mikegrattan]

I replaced all // with /* */ and it made no difference.  I stepped through the script in the debugger and I see the following values for variables:

vReplaces = Replace(1,top,StrCnd(null)),Replace(1,top,StrCnd(null)),Replace(2,top,StrCnd(null))

vRemoveRows = Remove(Row,Pos(Top,1))

vHFieldList = @1 as HDim1,@2 as HDim2,@3 as HDim3

vVDims = 1

vHDims = 3

vType = "ooxml"

I like the idea of putting the whole crosstable command as a variable so I'll try that next (if I can get the other issue fixed first).  It seems that the Load command doesn't like filtered variables....probably need to open a ticket with tech. support.

[marcus_sommer]

Last week we had a similar case with nested variables by one of our local usergroup meetings and the cause of the issues were inappropriate quotes around some variables and field-values. In your case I think the quotes here: vType = "ooxml" are wrong and it should be rather:

set vType = ooxml;

or

let vType = 'ooxml';

- Marcus

[mikegrattan]

Thanks for the additional thoughts on this issue Marcus.  I did verify that my variables are defined with single quotes and I am not using full quotes for any variables.  I don't think it works with no quotes, and the single quotes should be correct.

Perhaps this is a bug in Sense...I've opened a case with tech support and I'm hoping they can shed a little light on the matter.

Thanks!

Mike