topic How Trasform Row in Column??? in QlikView

How Trasform Row in Column???

fedcas — Wed, 29 Sep 2010 15:30:55 GMT

Hi all!

I have a big problem!

I'm loading a crm table where there are different contacts (Sales Manager, Edp Manager, etc) for the each customer.

I have to obtai a unique row for any Customer code with all contact.

Example:

Table A:

Customer	Contact	Type	Emal
abc ltd	mr Cooper	cfo	cooper@abc.com
abc ltd	mr Laives	cio	laives@abc.com
gold ltd	mrs Blues	edp	blues@gold.com
gold ltd	mrs Green	cio	green@gold.com
gold ltd	mrs Black	cfo	black@gold.com

New Table:

Customer	Contact1	Type1	Emal1	Contact2	Type2	Emal2	Contact3	Type3	Emal3
abc ltd	mr Cooper	cfo	cooper@abc.com	mr Laives	cio	laives@abc.com
gold ltd	mrs Blues	edp	blues@gold.com	mrs Green	cio	green@gold.com	mrs Black	cfo	black@gold.com

Every suggestion will be appreciate!!!!

Thanks!!!

How Trasform Row in Column???

pover — Wed, 29 Sep 2010 15:48:26 GMT

Do you want to see the new table in the GUI or in the data model?

And if you want it this way in the data model, why do you want to do something like this? Usually it is harder to make reports with such a table although there are always exceptions to the rule.

Regards.

How Trasform Row in Column???

Wed, 29 Sep 2010 15:52:54 GMT

Agree with Karl but sometimes there is exceptions really 🙂

If you really want to make it this way you may have a look at "generic load". See the link bellow for more info

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

How Trasform Row in Column???

Miguel_Angel_Baeyens — Wed, 29 Sep 2010 15:55:13 GMT

Hello Federico,

Try the solution provided by John Witherspoon in this post.

Regards.

How Trasform Row in Column???

pover — Wed, 29 Sep 2010 16:25:37 GMT

I'd go with the generic load approach mentioned by Stefan, although you are going to have to create a column that contains the 1,2,3 etc. For that you can try something like the following (not tested) when you load Table A.

Load Customer, Contact, Type, Email,

if(rowno()=1 or Customer <> previous(Customer), 1, previous(CustomerContactRowNo)+1) as ContactRowNo

Resident [Table A];

If previous(Customer) doesn't work try peek('Customer').

Edit: Sorry, I forgot to mention that you have to order Table A first by Customer before using the above script.

Regards.

How Trasform Row in Column???

fedcas — Wed, 29 Sep 2010 16:55:49 GMT

Thank af all for your precious help!

I tried Generic Load with last ppost of Karl to create the sequence, but just on this instruction there if a problem. In the first record Qv answer me " didn't find field ContacRowNo !"

So, How do I use "peek"?

Thanks

How Trasform Row in Column???

fedcas — Wed, 29 Sep 2010 17:05:39 GMT

FOUND!

(rowno()=1 or Customer <> previous(Customer), 1, numsum( 1, peek( 'ContactRowNo' ) )) as ContactRowNo

THANKS!!!!!!

Now I'll try the rest of the problem...

How Trasform Row in Column???

fedcas — Wed, 29 Sep 2010 17:31:44 GMT

GOAL!!!!

THANK YOU VERY MUCH AT ALL!!!!

I hope to return the favor as soon as possible!

Ciao

How Trasform Row in Column???

fedcas — Wed, 29 Sep 2010 17:31:53 GMT

GOAL!!!!

THANK YOU VERY MUCH AT ALL!!!!

I hope to return the favor as soon as possible!

Ciao

How Trasform Row in Column???

llauses243 — Wed, 29 Sep 2010 18:06:18 GMT

Hi Fede,

This is my simple offer

good luck , Luis