topic Re: Get specific value from table by a field name in QlikView

Get specific value from table by a field name

Mon, 30 Sep 2013 13:01:46 GMT

Hello ,

I have this tables in my project:

Main_Table:

[Part Family Name]	[Child1]	[Child2]	[Child3]	[Type]	[Type #]
AA1111	AA1	AA12345	AA6548	M1	1
BB2355	BB98744	BB65432	BB6581111	M1	2
CC24	CC55555	CC874654	CC988877	M2	1
DD235488	DD88452	DD46888	D2	M2	2

More_Details_Table:

[Part Name_A]	[Type]	Some other more columns...
AA1	M1	...
AA12345	M1	...
AA6548	M1	...
BB98744	M1	...
BB65432	M1	...
BB6581111	M1	...
CC55555	M2	...
CC874654	M2	...
...	...	...

Balance_Table:

[Part Name_A]	Balance
AA1	200
AA12345	11
AA6548	100
BB98744	8
BB65432	81
BB6581111	82
CC55555	83
CC874654	84
...	...

* The Main_Table connected to More_Details_Table by the Type field (key).

* The More_Details_Table connected to Balance_Table by the [Part Name_A] field (key).

I created some straight table with:

1 Dimension: [Type], [Type #]

3 Expressions

* My selection is on the [Type] field, for example I selected and filtered the Types and show only M1 Type:

The Straight table now looks like this:

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1
M1	2

On the expressions I'm trying to display this content:

On each cell I want the associated Child, for example on First Level column:

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1	AA1
M1	2	BB98744

Additionally, I need to add a second row on each cell that contain the balance that associated to the specific Child name, for example:

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1	AA1 200
M1	2	BB98744 8

[Type]

[Type #]

First Level

Second Level

Third Level

AA1

200

BB98744

I tried to write something like:

=[Child1] & Chr(13) &

only({$<[Part Name_A] = p([Child1]) >} [Balance])

but got only the Child name (AA1 on the first cell and BB98744 on the second)...

Also I tried to use FieldValue function but the position of each value changed when I have lots of Types...

please help me with this...

This is the final table I need to get (after selected and filtered the Type field to 'M1' value):

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1	AA1 200	AA12345 11	AA6548 100
M1	2	BB98744 8	BB65432 81	BB6581111 82

[Type]

[Type #]

First Level

Second Level

Third Level

AA1

200

AA12345

AA6548

100

BB98744

BB65432

BB6581111

Many Thanks for your support,

Itsik

Re: Get specific value from table by a field name

Anonymous — Mon, 30 Sep 2013 13:29:31 GMT

See if this helps..

=[Child1] & Chr(13) & MaxString({$<[Part Name_A] = p([Child1]) >} [Balance])

=[Child1] & Chr(13) & Max({$<[Part Name_A] = p([Child1]) >} [Balance])

Re: Get specific value from table by a field name

Mon, 30 Sep 2013 13:59:02 GMT

This returns me the same balance (the last child balance) to the two cells...

like this:

[Type] [Type #] First Level Second Level Third Level

---------------------------------------------------------------------------------------------

M1 1 AA1

M1 2 BB98744

Re: Re: Get specific value from table by a field name

Anonymous — Mon, 30 Sep 2013 15:01:01 GMT

Try this.

Hope it helps

Re: Get specific value from table by a field name

Anonymous — Mon, 30 Sep 2013 15:02:44 GMT

Hello Itsik.

Try

Child1 & Chr(13) & FieldValue('Balance', FieldIndex ( 'PartName_A', Child1 ))

Re: Get specific value from table by a field name

Mon, 30 Sep 2013 17:47:09 GMT

Hi Lucas,

The expresiion gives me some wrong balance values...

The strange thing is when I put all the expression:

=Child1 & Chr(13) & FieldValue('Balance', FieldIndex ( 'PartName_A', Child1 ))

I got some right values of the Child1 before Chr(13) but wrong values at the balance...

And if I cut the begining of the expression and write only:

=FieldValue('Balance', FieldIndex ( 'PartName_A', Child1 ))

I've got the same balance at two cells a wrong one, and different from the two that was before with the complete expression...

Re: Re: Get specific value from table by a field name

Anonymous — Mon, 30 Sep 2013 18:36:58 GMT

Hello Itsik,

That is weird, with the examples that I got from your model, I could reach the exact result that you desired.

Please, try loading your datamodel in the Attached file ( has the same columns names as yours ), to see if you get any wrong result.

Re: Get specific value from table by a field name

Tue, 01 Oct 2013 05:48:50 GMT

I'll Do one more check and Upload my file if it dont success...

Re: Re: Get specific value from table by a field name

Anonymous — Tue, 01 Oct 2013 11:11:45 GMT

Ok Itsik,

Here is what are the values on my example ( and I attached the file again, just in case 😞

Type	Type #	First Level	First Level - Balance	Second Level	Second Level - Balance	Third Level	Third Level - Balance
M1	1	AA1 200	200	AA12345 11	11	AA6548 100	100
M1	2	BB98744 8	8	BB65432 81	81	BB6581111 82	82
M2	1	CC55555 83	83	CC874654 84	84	CC988877
M2	2	DD88452		DD46888		D2

Re: Re: Get specific value from table by a field name

Tue, 01 Oct 2013 12:35:40 GMT

Your example is really work, but in my project, with my data it didnt... I dont know why...

I'll check with my company if I can upload some of the real data to show you...

Thank you for everything so far...

Re: Re: Get specific value from table by a field name

Anonymous — Tue, 01 Oct 2013 17:11:26 GMT

You are welcome Itsik, I will be waiting for new data then.

Maybe you could send me just a little records, where the formula is not working.

Regards