topic Re: Unpivot/CrossTable question - how do I do it?! in QlikView

Unpivot/CrossTable question - how do I do it?!

Mon, 23 Jun 2014 14:32:43 GMT

Hi,

I've been trying to unpivot some data in my QlikView load script and I just can't get the syntax right. I'm pulling the data from an Oracle SQL database so I'm not sure whether I'm best using Unpivot or CrossTable.

In its raw form, the SQL that I'm pulling in is something like this:

SQL SELECT "AMOUNT_1",
    "AMOUNT_2",
    "AMOUNT_3",
    "AMOUNT_4",
    "AMOUNT_5",
    "AMOUNT_6",
    "AMOUNT_7",
    "AMOUNT_8",
    "AMOUNT_9",
    "AMOUNT_10",
    "AMOUNT_11",
    "AMOUNT_12",
    "BUDGET_YEAR" as "Budget Year",
    "DATE_YYYYMMDD" as "Budget Date",
    DESCR as "Budget Narrative",
    DSET as "Dataset",
    GLCODE as "GL Code",
    JVREF as "Budget JV Ref",
    JVTYPE as "Budget JV Type"
FROM LIVE.NMLBJDET where DSET='03';

What I need is a table with the data for amounts 1 through to 12 in the same column, with a column showing AMOUNT_1, AMOUNT_2 etc. (or even better, 141501 for AMOUNT_1, which would be a combination of BUDGET_YEAR and the 1 from the column name. These column names won't change so if it's easiest to hard code something in that's fine.

This is for a QlikView document showing budgets from the General Ledger.

I'd appreciate any help that any of you QlikView gurus can provide!

Dave

Re: Unpivot/CrossTable question - how do I do it?!

Anonymous — Mon, 23 Jun 2014 14:38:51 GMT

Did you tried loading as a cross table?

Re: Unpivot/CrossTable question - how do I do it?!

erikzions — Mon, 23 Jun 2014 14:39:19 GMT

if you want to do it in Qlikview this is one way. Make sure you add the key field obviously.

Pivot:

Load

"AMOUNT_1"

'Amount_1' as Type

from LIVE.NMLBJDET where DSET='03'

where AMOUNT_1 is not null;

Concatenate (Pivot)

Load

"AMOUNT_2"

'Amount_2 as Type

from LIVE.NMLBJDET where DSET='03'

where AMOUNT_2 is not null;

etc. etc. etc.

Re: Unpivot/CrossTable question - how do I do it?!

Mon, 23 Jun 2014 14:45:20 GMT

Thanks Erik.

I've just tried this and it's not picking up the data from the SQL database (the error is Cannot open file 'O:\MAHOME\Man Accts Databases\QlikView\LIVE.NMLBJDET where DSET='03' where AMOUNT_1 is not null' The system cannot find the file specified.).

Normally with an SQL load I'd put SQL SELECT before the fields that I want to pull in, but I can't work out where this should go.

Sorry, I'm a relative newbie!

Re: Unpivot/CrossTable question - how do I do it?!

Mon, 23 Jun 2014 14:46:32 GMT

I tried CrossTable but just couldn't get the syntax to work, and I wasn't sure what the best way to achieve this was.

Re: Unpivot/CrossTable question - how do I do it?!

erikzions — Mon, 23 Jun 2014 14:58:42 GMT

Load everything into a temp table. Then use resident loads.

The cross table is also a good idea. Here is a good tutorial. http://community.qlik.com/docs/DOC-4981

Re: Unpivot/CrossTable question - how do I do it?!

maleksafa — Mon, 23 Jun 2014 15:02:22 GMT

use cross table in qlikview, to do that:

1) go to the script

2) select the qvd (preferred to have your sql query return the data in a qvd)

3) in the wizard click next twice until you find buttons under prefixes for corsstable...

4) in the crosstable view, define the qualifier fields which means the fixed field that you don't want to pivot/unpivot

5) in the attribute field name, give a name for the column

6) and the data field you can keep it as 'Data' and it will be the intersection between the fixed qualifiers and the attribute field.

Re: Unpivot/CrossTable question - how do I do it?!

Mon, 23 Jun 2014 15:28:36 GMT

Thank you. I had to work out what a QVD was and how to do it but this has solved my problem!