topic Split a row into multiple rows (not subfield) in QlikView

Split a row into multiple rows (not subfield)

Wed, 22 Dec 2010 11:31:11 GMT

Hi guys,

I have a question.

I have to split a single row with 3similar distinct fileds, lets say f1,f2,f3, into 3distinct rows with fields f and id (where id=1 if field is f1, id=2 if field is f2, id =3 if field is f3).

That is to say starting from a single row in my table I would like to load 10 distinct rows with an id number.

Example of initial row:

Customer_Id, Customer_Name, f1, f2, f3;

What I need is to obtain a table like this:

Customer_id, Customer_Name, id_field, f

with 3 different rows for each (Customer_Id, Customer_Name)

Is that possible? How can I obtain this?

Thanks to everybody and have a very Merry Xmas!

Split a row into multiple rows (not subfield)

Wed, 22 Dec 2010 12:32:00 GMT

Hi Elena,

you've got 2 ways at least to do it.
If the data comes from a file, load it using CROSSTABLE statement.
if the data is already loaded from an other table go through as many time you need either using a loop or a function with number argument (1, 2, 3) or simply but longer 3 tiles with a if like this:

for i = 1 to 3

load

customer_id, customer_name, f$(i) as id_f

from table

nexy

best regards

christian

Split a row into multiple rows (not subfield)

Anonymous — Wed, 22 Dec 2010 12:37:05 GMT

I'm sure thaere are better ways, this is just what I think about first:

FOR n=1 to 3
LOAD
Customer_id,
Customer_Name,
'$(n)' as id_field,
f$(n) as f
RESIDENT table;
NEXT

Split a row into multiple rows (not subfield)

Wed, 22 Dec 2010 13:39:52 GMT

Thanks a lot.

I hope there was a solution different from this. I have already tried to do a "for ... next" cycle but qlik seems not to appreciate it!!!!

So I was wondering if there is a better solution for this matter.

Split a row into multiple rows (not subfield)

Wed, 22 Dec 2010 15:17:21 GMT

Hi Elena,

I hope my example will give a solution to your problem.

Split a row into multiple rows (not subfield)

Wed, 22 Dec 2010 15:46:20 GMT

With the "for... next" method the script hangs... (the reload script time is about 3 minutes but it is freezed doing nothing since about 30 minutes....

I will try to use your method, Venugopal4s, also if actually I have 15 fields (not only 3)!

Split a row into multiple rows (not subfield)

johnw — Thu, 23 Dec 2010 02:25:49 GMT

I strongly recommend what Christian suggested - CROSSTABLE, which can actually be used regardless of the source of the data. Using the InitialTable from the example from venugopal4s, you'd do this instead of all the different table loads:

FinalTable:
CROSSTABLE (f,id,2)
LOAD
customer_id
,customer_name
,f1 as 1
,f2 as 2
,f3 as 3
RESIDENT InitialTable
;

This is easily extended to 15 fields, one line per field. The only thing even requiring us to list the fields above is that we decided we wanted values of '1' instead of 'f1' in field "f", though I make a habit of always listing my fields explicitly, so this wouldn't make any difference to me personally.

The only down side I'm aware of is that a crosstable load can take more memory in at least some cases than loading in a loop. As a result of that, one of my applications handled this sort of load with a loop for a long time - until we upgraded to 64-bit and added more memory to the server. I now always use crosstable for this sort of requirement.

Split a row into multiple rows (not subfield)

Thu, 23 Dec 2010 07:36:33 GMT

Hi Elena,

I tried with John solution, it worked like magic.

It's really more helpful to the beginners like me.

Thanks a lot John/christian.

Split a row into multiple rows (not subfield)

Thu, 23 Dec 2010 13:59:36 GMT

Thanks a lot John.

I tried with crosstable, it seemed to me a good solution but... my scipt hangs after loading the crosstable. Is is since about 2 hours that is waiting....(see the image below).

I don't' know how to solve my problem...

Split a row into multiple rows (not subfield)

johnw — Mon, 27 Dec 2010 18:38:32 GMT

You might be doing a crosstable on more fields than you intended, so are getting more rows than you intended. It's also possible that you simply don't have enough memory to convert the columns to rows using crosstable, and need a better computer or a less memory-intensive solution. But it's hard to debug remotely with no other information than "my script hangs".

Edit: Perhaps try only loading, say, a thousand rows of the data. If the crosstable works and produces the table structure you want, then it's probably a memory problem. If it produces something other than what you want, then you have a script problem to debug.

Split a row into multiple rows (not subfield)

Wed, 09 Mar 2011 18:06:21 GMT

@All,

Thanks for the content of this thread, certainly helped me alot

I've also had some problems using CrossTable and haning today this has helped.

I've also discovered that when reading data from an xlsx that the colummn names need to be text not numeric when using CrossTable although they don't need to be if you do a simple LOAD!