topic Re: Rows to Columns dynamically without Cross Table in QlikView

Rows to Columns dynamically without Cross Table

Anonymous — Wed, 23 Sep 2015 12:05:38 GMT

Dear Experts,

I would like to develop a script which converts the rows into columns dynamically

Since the data is huge the Cross Table is not the optimized way and the Customer don't want the same thing in the layout by Pivot.

Attaching a sample data and the kind of output needed for your kind reference.

Kindly help

Thanks & Regards

Jeba

Re: Rows to Columns dynamically without Cross Table

alexandros17 — Wed, 23 Sep 2015 12:15:47 GMT

You could use Left Join un the table filtered by the row value you need as column ....

Re: Rows to Columns dynamically without Cross Table

jonathandienst — Wed, 23 Sep 2015 12:16:32 GMT

Jebamalai Dass wrote:

Since the data is huge the Cross Table is not the optimized way

Have you tested this assertion? The cross table operation converts the data into a proper fact table that will perform better than the original cross table and will greatly simplify the dimensions and expressions in the front end.

While you could achieve this manually, I very much doubt that what you do will outperform the QV crosstable function.

Re: Rows to Columns dynamically without Cross Table

Anonymous — Wed, 23 Sep 2015 12:20:28 GMT

Hi Alessandro,

All the fields are in the same qvd.

Does this mean we need to load twice and left join?

Regards

Jeba

Re: Rows to Columns dynamically without Cross Table

Anonymous — Wed, 23 Sep 2015 12:21:18 GMT

Hi Jonathan,

Thanks for the guidance,

I tried that but it took huge time and was not responding

Thanks & Regards

Jeba

Re: Rows to Columns dynamically without Cross Table

alexandros17 — Wed, 23 Sep 2015 12:23:20 GMT

Not twice but as many times as the different values you need as column (you can use a loop after you tested for a column)

Re: Rows to Columns dynamically without Cross Table

Anonymous — Wed, 23 Sep 2015 12:53:10 GMT

Hi Alessandro,

Is there any examples you can share with me?

Regards

Jeba

Re: Rows to Columns dynamically without Cross Table

marcus_sommer — Wed, 23 Sep 2015 13:03:06 GMT

I think jontydkpi is right. In addition to them it might be helpful to split the crosstable-loadings in cases that there isn't enough RAM (it shouldn't be swapped into the virtual RAM) and/or to use incremental loads. Here you find various links to them: Advanced topics for creating a qlik datamodel

- Marcus

Re: Rows to Columns dynamically without Cross Table

jonathandienst — Wed, 23 Sep 2015 13:57:23 GMT

You probably ran out of memory - what makes you think that you can do it using less memory? This is a function of your data set and server RAM. You may have to reduce the one or increase the other.

But perhaps you should post the code that your tried. Please include the field names (ie not LOAD *) for diagnostic purposes.

Re: Rows to Columns dynamically without Cross Table

Anonymous — Wed, 23 Sep 2015 15:04:06 GMT

Hi Jonathan,

I will try another time

May be a issue in the cross table logic I used

Thanks for your time

Regards

Jeba

Re: Rows to Columns dynamically without Cross Table

petter — Wed, 23 Sep 2015 19:24:34 GMT

This should give you an idea of how to do pivot in script dynamically: