topic Re: Create new table from existing data in QlikView

Create new table from existing data

Mon, 03 Dec 2012 10:29:40 GMT

Hi all,

I have some data in Excel, structured as a Pivot, containing 2 fields, with a value per combination - like the example below:

	TIME
Field X	t0	t1	t2
AA	1	2	3
BB	1	2	3
CC	1	2	3
XX	1	2	3
YY	1	2	3
ZZ	1	2	3

Whereby "Field X" and "Time" are the fields, and the values (in this example) are all 1, 2, and 3.

But what I want is to convert this table into something usable in QV, so that all combinations of values are automatically prepared in a new table in the script, like so:

FIELD X	TIME	VALUE
AA	t1	1
AA	t2	2
AA	t3	3
BB	t1	1
BB	t2	2
BB	t3	3

I understood that Loop could help, but no idea on how to actually script this, as there are hundreds of values with long historical values to include.

Any ideas?

Thanks!

Re: Create new table from existing data

MayilVahanan — Mon, 03 Dec 2012 10:38:29 GMT

Try like use crosstable:

crosstable(time,value)

load * from tablename;

Hope it helps

Re: Create new table from existing data

Mon, 03 Dec 2012 11:08:36 GMT

Hi Mayil,

That function seems to do what I want, but I seem to have a problem in the script (shows a Syntax error)...

I used the Crosstable wizard to generate the script, but the whole Crosstable section shows up as an error (including the CROSSTABLE function itself...)

Any ideas why?

Re: Create new table from existing data

jonathandienst — Mon, 03 Dec 2012 11:12:54 GMT

If you want help in debugging your script, it would be a help if you posted the relevant portion of the script.

Regards

Jonathan

Re: Create new table from existing data

MayilVahanan — Mon, 03 Dec 2012 11:14:59 GMT

http://community.qlik.com/message/134713#134713

http://community.qlik.com/message/82902#82902

Example for crosstable..

Please check the syntax in this..

Re: Create new table from existing data

Mon, 03 Dec 2012 11:29:46 GMT

Thanks Mayil,

Silly error - used bad fieldnames in the script!

Made a small amendment and now it works!

Thanks