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

[#1]

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

[Anonymous]

Did you tried loading as a cross table?

[erikzions]

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.

[#4]

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!

[#5]

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.

[erikzions]

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

[maleksafa · Accepted solution]

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.

[#8]

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