Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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.
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
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.
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
FOUND!
if
(rowno()=1 or Customer <> previous(Customer), 1, numsum( 1, peek( 'ContactRowNo' ) )) as ContactRowNo
THANKS!!!!!!
Now I'll try the rest of the problem...
GOAL!!!!
THANK YOU VERY MUCH AT ALL!!!!
I hope to return the favor as soon as possible!
Ciao
GOAL!!!!
THANK YOU VERY MUCH AT ALL!!!!
I hope to return the favor as soon as possible!
Ciao