topic Re: Database structure problem in QlikView

Database structure problem

Tue, 05 Jul 2011 09:21:21 GMT

Hi everyone,

I am evaluating QV, and i was trying to create some document...

But i have a problem regarding my DB structure:

I have a table that contains some data which refers to hour and day of the year, but instead of having a "classical" columns structure (with a column for data, a column for hour, and a row for every value), we have 24 columns representing the 24 hours in a day, thus every row containing the 24 values of that day

Now how can i create a QV graph (for example a linear graph - i don't know if it is the exact name, i have an italian installation of QV) that shows the values as expression (y axis of the graph) and the hours as dimensione (x axis)?

I played around a bit but can't figure out how to make it work

Any suggestions?

Thanks in advace,

Maikol

Database structure problem

Thu, 07 Jul 2011 14:26:35 GMT

C'mon, no one ever faced this kind of problem? =(

There must be a way to make it work

Database structure problem

nagaiank — Thu, 07 Jul 2011 14:31:44 GMT

I think you can load data as a Cross Table.

If you attached file giving sample data and state what sort of output you want to see, the forum will be able to help.

Re: Database structure problem

johnw — Thu, 07 Jul 2011 23:21:54 GMT

krishnamoorthy wrote:
I think you can load data as a Cross Table.

I agree. Search for "Crosstable" in the help text index. I've attached a random example.

Re: Database structure problem

Fri, 08 Jul 2011 07:03:38 GMT

Yes it surely must be this cross table you mentioned

I gave a read to the QV tutorial, and the last example in the crosstable section is exactly what i need:

I have a DB table with some "regular" columns, and then the crossed data (in the cons_h1, cons_2, ... , consh24 columns, whihc are obviously the hours in a day)

CONS_UTE CONS_CODSERV CONS_DATA CONS_TIPODATO CONS_ID_PRA CONS_TIPOFORN CONS_TENSIONE CONS_TIPOCLIENTE CONS_H1 CONS_H2 CONS_H3 CONS_H4 CONS_H5 CONS_H6 CONS_H7 CONS_H8 CONS_H9 CONS_H10 CONS_H11 CONS_H12 CONS_H13 CONS_H14 CONS_H15 CONS_H16 CONS_H17 CONS_H18 CONS_H19 CONS_H20 CONS_H21 CONS_H22 CONS_H23 CONS_H24 CONS_H3BIS

PD 1000817 01.01.2011 M 0 ND BT M 2,3056 2,1372 1,9531 1,7855 1,6831 1,6636 1,7426 1,8346 1,9622 2,1462 2,3722 2,5794 2,5694 2,4286 2,4067 2,3944 2,5656 2,9964 3,138 3,1712 3,0685 2,8834 2,56 2,2222

The problem is that the tutorial example is made with excel-loaded data, using some particular functions (autocomposition or something like that)
I have some DB data instead, so i don't know how to write the code to correctly extract them

My code is actually this:

CONSUMI:
ODBC CONNECT TO [ver;DBQ=ver] (XUserId is ZDDXEXVMULYIDcRU, XPassword is MZWOFXVMULYIDcJG);
SQL SELECT * FROM NETA4X."EF_CONSUMI24" 
where cons_ute = 'PD' 
and cons_data >= to_date('01012011','ddmmyyyy')
and cons_data < to_date('11012011','ddmmyyyy')
and cons_codserv like '11%';
load CONS_DATA,
month(CONS_DATA) as Mese,
Year(CONS_DATA) as Anno,
Day(CONS_DATA) as Giorno,
WeekDay(CONS_DATA) as Giorno_della_settimana,
Week(CONS_DATA) as Settimana
Resident CONSUMI;

And i would like to add a column for the hours and a column for the values (but i guess i have tho change what i already wrote)

Thanks for any advice

Re: Database structure problem

Fri, 15 Jul 2011 06:41:04 GMT

Ok i have found a way to get my data, i just have to add:

CrossTable (Ora, Valore, 1)

Load

CONS_DATA,

CONS_H1 as 1,

CONS_H2 as 2,

CONS_H3 as 3,

CONS_H4 as 4,

CONS_H5 as 5,

CONS_H6 as 6,

CONS_H7 as 7,

CONS_H8 as 8,

CONS_H9 as 9,

CONS_H10 as 10,

CONS_H11 as 11,

CONS_H12 as 12,

CONS_H13 as 13,

CONS_H14 as 14,

CONS_H15 as 15,

CONS_H16 as 16,

CONS_H17 as 17,

CONS_H18 as 18,

CONS_H19 as 19,

CONS_H20 as 20,

CONS_H21 as 21,

CONS_H22 as 22,

CONS_H23 as 23,

CONS_H24 as 24,

CONS_H3BIS as 25

Resident CONSUMI;

As you see i hav renamed the varoius column names to make them look like the hours in a day

But this way the ehngine doesn't consider them properly
Because if i have another value to be displayed on the hours (and for example i want to show both values on a graph), this kind of renaming doesn't work

Any idea?

Thanks