
Database structure problem
Maikol Miorandi Jul 7, 2011 10:26 AM (in response to Maikol Miorandi)C'mon, no one ever faced this kind of problem? =(
There must be a way to make it work

Database structure problem
Nagaian Krishnamoorthy Jul 7, 2011 10:31 AM (in response to Maikol Miorandi)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
John Witherspoon Jul 7, 2011 7:21 PM (in response to Nagaian Krishnamoorthy)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.

testCrosstable6.qvw 123.0 K




Re: Database structure problem
Maikol Miorandi Jul 8, 2011 10:42 AM (in response to Maikol Miorandi)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 excelloaded 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 themMy 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
Maikol Miorandi Jul 15, 2011 2:41 AM (in response to Maikol Miorandi)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 dayBut 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 workAny idea?
Thanks
