topic Re: One to many in QlikView

One to many

Wed, 25 Nov 2020 16:16:04 GMT

I have a database with one to many relationship.

Its two tables, customerid and ContactInfo, the key in the tables are customerid.

In the Contactinfo table there is a field "value"

If I load the tables in a straight chart and uses the CustomerId as dimension the results looks like this:

CustomerId	ContactInfo	Value
1	Name	Test 1
1	Adress	Test street
2	Name	Test 2

What I would like to achieve is a result that looks like this:

CustomerId	Name	Adress
1	Test 1	Test street
2	Test 2	Test street

Re: One to many

vishsaggi — Mon, 06 Mar 2017 18:28:50 GMT

Try using a pivot table and add

Dimensions:

CustomerId

ContactInfo

Expr:

= IF(Len(Trim(Value)) = 0, 'Test Street', Value)

Then move your ContactInfo to top of the expression. Something like this.

Re: One to many

Mon, 06 Mar 2017 19:47:52 GMT

Very helpful, thank you!

But if the contact info includes for ex Telephone number and I dont want to show that field, is there a way in the expression to decide which contactinfo values that will show in the chart?

Re: One to many

vishsaggi — Mon, 06 Mar 2017 20:02:57 GMT

Try this?

= Pick(Match(ContactInfo, 'Name', 'Adress'), IF(Len(Trim(Value))=0, '',Value), IF(Len(Trim(Value))=0, 'Test Street',Value))

Re: One to many

MarcoWedel — Mon, 06 Mar 2017 20:19:41 GMT

You could try with a generic load

Re: One to many

sasiparupudi1 — Mon, 06 Mar 2017 20:22:02 GMT

Use Generic Load

t1:

Load * Inline

[

CustomerId,ContactInfo,Value

1,Name,Test 1

1,Adress,Test street

1,Telphone,Test street

2,Name,Test 2

];

GenericCust:

Generic LOAD

CustomerId,ContactInfo,Value

Resident t1;

drop Table t1;

hth

Sas

Re: One to many

muthukumar77 — Tue, 07 Mar 2017 05:25:46 GMT

Hi,

Refer this app,