topic Re: Combining Values from a Field in QlikView

Combining Values from a Field

Thu, 18 Oct 2012 17:26:41 GMT

Hi-

I have a ItemId code field that corresponds to an ItemName field. The ItemID field can have more than one ItemName. My problem is that I want this relationship to be 1 to 1. Is there a way to combine the ItemNames so that after I load the data, if I select an ItemID that has more than 1 ItemName, it only shows 1 ItemName?

Thank you

Re: Combining Values from a Field

swuehl — Thu, 18 Oct 2012 17:36:32 GMT

You can use a group by load and one of the aggregation functions, for example Minstring(), to pick one of the ItemName values per ItemID. The question is, which one?

edit:

LOAD

ItemID,

minstring(ItemName) as OnlyOneItemName

resident TABLE group by ItemID;

Re: Combining Values from a Field

MayilVahanan — Thu, 18 Oct 2012 17:40:31 GMT

ItemTest:

Load * Inline

[

ItemId,ItemName

1,aaa

1,bbb

1,ccc

2,ddd

2,eee

2,fff

];

load ItemId,MaxString(ItemName) as IName Resident ItemTest Group by ItemId;

Hope it helps

Re: Combining Values from a Field

Thu, 18 Oct 2012 17:52:20 GMT

The ItemName that is chosen is arbitrary, as they have the same meaning.

Example, an ItemName that has two values. First, called Item1. Second value called Item(1).

Re: Combining Values from a Field

Thu, 18 Oct 2012 17:54:29 GMT

Hi-

The ItemName and ItemID fields contain thousands of values, so I'd rather not go through and list each item Inline as per the suggestion.

Re: Combining Values from a Field

MayilVahanan — Thu, 18 Oct 2012 17:58:14 GMT

For example, i used like that..

try like this

Load ItemNo,MaxString(ItemName) as ItemName from table group by ItemNo;

Hope that helps

Re: Combining Values from a Field

Fri, 19 Oct 2012 14:27:15 GMT

If ItemID is in Table1, and ItemName is in Table2, how then would I script that?

Re: Combining Values from a Field

swuehl — Fri, 19 Oct 2012 17:36:15 GMT

There is a linking field between ItemID and ItemName, connecting the two tables, right?

In a simple case, just use this field to group by your Table2.

Can you tell us a bit more about your table structure?

Re: Combining Values from a Field

Fri, 19 Oct 2012 18:51:14 GMT

I had to join a couple of tables in order to get the structure correct. Once I did that, your original solution worked perfectly.

Thank you.