topic Re: MergeMultiple rows into columns in QlikView

MergeMultiple rows into columns

venkatg6759 — Tue, 23 Feb 2016 16:27:05 GMT

Hello Everyone,

I have below table

Desc	Val
Errors	1
Category	Loan
Sub Category	Loan Sold
Label	Record Count

I Need to separate and make it show like this

Errors Category Sub Category Label

1 Loan Loan Sold Record Count

Can anyone help me how to do this.

Thanks in advance,

Venkata

Re: MergeMultiple rows into columns

sunny_talwar — Tue, 23 Feb 2016 16:30:32 GMT

Try this:

Table:

Generic

LOAD 1 as Field,

Inline [

Desc, Val

Errors, 1

Category, Loan

Sub Category, Loan Sold

Label, Record Count

];

DROP Field Field;

Re: MergeMultiple rows into columns

Clever_Anjos — Tue, 23 Feb 2016 16:30:52 GMT

Is your table

A source (excel, sql, etc)
An object?

Re: MergeMultiple rows into columns

venkatg6759 — Tue, 23 Feb 2016 16:38:56 GMT

It's not excel.It's coming from SQL.these are just 2 columns in that table.

Re: MergeMultiple rows into columns

Tue, 23 Feb 2016 16:59:52 GMT

Hi Venkat,

What is the DB you are using like Oracle, MySQL etc..

Thanks,
Sreeman

Re: MergeMultiple rows into columns

Colin-Albert — Tue, 23 Feb 2016 17:01:03 GMT

Generic Load can fix your data as suggested by Sunny T sunindia‌

This post has more details The Generic Load

Re: MergeMultiple rows into columns

johnw — Tue, 23 Feb 2016 17:15:29 GMT

Yet another example of a generic load. This is from one of my live applications, with a little unneeded complexity removed, hopefully without breaking it. Same idea as Colin_Albert‌'s link to hic's generic load, where the columns are all combined into one table instead of left as separate tables, just a slightly different approach to building that final table.

// Creates one Generic.* table per Facility Event LCID/Class
[Generic]:
GENERIC
LOAD
[Order Item]
,[Facility Event LCID/Class]
,[Manufacturing Result]
FROM ManufacturingInstructions.qvd (QVD)
;

// Build new flat table with distinct order items.
// This is faster than a load distinct.
[Flat]:
LOAD text(fieldvalue('Order Item',recno())) as [Order Item]
AUTOGENERATE fieldvaluecount('Order Item')
;
// For each table that still exists
LET i = 0;
DO WHILE i < nooftables()
    LET vTable = tablename($(i));
    // If it's one of our Generic.* tables
    IF subfield(vTable,'.',1)='Generic' THEN
        // Add the field to our flat table.
        LEFT JOIN ([Flat])
        LOAD *
        RESIDENT [$(vTable)]
        ORDER BY [Order Item]
        ;
        // And delete the Generic.* table
        DROP TABLE [$(vTable)]
        ;
    ELSE
        // Not a Generic.* table. Skip past it.
        LET i = i + 1;
    ENDIF
LOOP

Re: MergeMultiple rows into columns

venkatg6759 — Tue, 23 Feb 2016 21:48:46 GMT

Will try this .But Generic Load worked for me .Later I merged column tables to main table by join.Thank you John

Re: MergeMultiple rows into columns

venkatg6759 — Tue, 23 Feb 2016 21:49:51 GMT

This was helpful colin .Thanks

Re: MergeMultiple rows into columns

johnw — Tue, 23 Feb 2016 21:52:59 GMT

If the generic load serves your needs, you're done. I wouldn't introduce complicated scripting like I show above to combine the fields into one table. I needed it so that I could store the result as a QVD. If you don't have that or another compelling reason to combine the fields, just leave them like the generic load builds them. I really should have mentioned that in my original post, but I wasn't thinking it through, just grabbing an example.

Re: MergeMultiple rows into columns

MarcoWedel — Tue, 23 Feb 2016 22:34:11 GMT

Hi,

another version of a script to recombine tables generated by a generic load into one can be found here:

Use cases for Generic Load | Qlikview Cookbook

by Rob Wunderlich

hope this helps

regards

Marco