Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Highlighted
mikegrattan
Contributor II

Excel file with multiple header rows and commas for decimals

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Excel file with multiple header rows and commas for decimals

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

9 Replies
Partner
Partner

Re: Excel file with multiple header rows and commas for decimals

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.

MVP & Luminary
MVP & Luminary

Re: Excel file with multiple header rows and commas for decimals

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
Contributor II

Re: Excel file with multiple header rows and commas for decimals

Thank you Steven; I think this will be helpful.

mikegrattan
Contributor II

Re: Excel file with multiple header rows and commas for decimals

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
Contributor II

Re: Excel file with multiple header rows and commas for decimals

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:

qlik_headers_file_script_error.jpg

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


Thanks.

MVP & Luminary
MVP & Luminary

Re: Excel file with multiple header rows and commas for decimals

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
Contributor II

Re: Excel file with multiple header rows and commas for decimals

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.

MVP & Luminary
MVP & Luminary

Re: Excel file with multiple header rows and commas for decimals

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
Contributor II

Re: Excel file with multiple header rows and commas for decimals

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