Split a row into multiple rows (not subfield)

[#1]

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!

[#2]

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

[Anonymous]

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

[#4]

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.

[#5]

Hi Elena,

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

[#6]

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)!

[johnw]

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.

[#8]

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.

[#9]

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...

[johnw]

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.