topic Re: Advanced data transformation in QlikView

Advanced data transformation

datanibbler — Wed, 22 Jan 2014 15:01:33 GMT

Hi,

I have some base data like I've provided in the Excel file attached.

- That block of data is part of a much larger populated area on that sheet.

=> Currently, I load that into QlikView by

- conditionally removing all the lines that do not equal the text in column_2

- transposing the whole thing

- removing line_1

<=> The issue is, there are six or seven such three-line-paragraphs for this year. Currently I have to write expressions into my chart for each of these blocks and make it dependent on a variable that is governed by a button.

=> Isn't there a way I could transform this data in the script so that I have two dimensions

- date

- gate_nr

and three pieces of data each:

- available slots

- pre-booked slots

- used slots

That would make my chart a lot more elegant:

- Less individual formulas

- No need for that button

- Possibility to build a trellis_chart.

Thanks a lot!

Best regards,

DataNibbler

Re: Advanced data transformation

Michiel_QV_Fan — Wed, 22 Jan 2014 15:06:06 GMT

Can you add a field with the different slots to each excel load, something like 'available' as slot and 'pre-booked' as slot.

Then you can add slot to your chart.

Re: Re: Advanced data transformation

datanibbler — Wed, 22 Jan 2014 15:16:01 GMT

Hi Michiel,

well, in principle - oops, I forgot to actually attach my sample.

In principle, all I'd have to do is add a field "gate_nr" to the left of the end_result (after transposing and stuff, which is exactly the problem), then I'd have my second dimension.

=> The problem is I just don't know how to do that with all the "remove, transpose, remove" stuff - can I do these things in a RESIDENT LOAD? That would simplify things - I could just load that block of data as it is, without any transformation, then load it RESIDENT and transpose it and then load it RESIDENT once more and add that field on the left.

Any ideas?

Thanks a lot!

Best regards,

DataNibbler

Re: Advanced data transformation

datanibbler — Wed, 22 Jan 2014 15:32:51 GMT

I can think of a way, maybe:

- Do six separate LOADs from the Excel base_file, complete with all the transformation_steps, each one for only one
gate

- Append all of them

- Load it all RESIDENT and generate that extra field.

<=> Isn't there an easier way? This looks like quite an effort to me...

P.S.: It's even more complex: I have to

- do one LOAD for every gate, complete with all the transformation_steps

- use Noconcatenate inbetween to have so many separate tables

- Then do RESIDENT LOADs for all of them

- concatenate all of those

- generate one more field with the gate_Nr

- rename the fields to not include the gate_Nr anymore

- last, but not least, drop all of those separate tables

Best regards,

DataNibbler

Re: Advanced data transformation

Michiel_QV_Fan — Thu, 23 Jan 2014 08:05:06 GMT

Can you post an example?

Re: Advanced data transformation

datanibbler — Thu, 23 Jan 2014 11:29:42 GMT

Hi Michiel,

don't bother. I'm already nearly through, in the way I have thought of. That's some typework, but it makes the data_model much nicer and more usable.

So ´just regard this as solved.

Best regards,

DataNibbler