Advanced data transformation

[datanibbler]

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

[Michiel_QV_Fan]

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.

[datanibbler]

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

[datanibbler]

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

[Michiel_QV_Fan]

Can you post an example?

[datanibbler]

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