topic Return specific value from straight table in QlikView

Return specific value from straight table

NewToQV — Wed, 06 Mar 2019 13:23:30 GMT

I have a straight table where I have 4 dimensions and multiple expressions just showing the value of a cell from a staged excel file.

I need to return specific values through out the straight table. The table has been frozen so it cannot be sorted etc.

Example table below. My 4 dimensions are as of date, partner, partner product, min.max.

I need to be able to pull specific Need values in the columns.

Example: I need to pull the a.2 min needed value 1 and display it in a text object

Then in another text object I need to display the A.2. max value in a text object.

So on and so forth.

Thank you ahead of time!

As Of Date	Partner	Partner Product	Min/Max	Needed values 1	Needed values 2	Needed values 3	Needed values 4
1/1/2018	A	A.1	Min	test 1	value 1	needed 1	last 1
1/1/2018	A	A.1	Max	test 2	value 2	needed 2	last 2
1/1/2018	A	A.2	Min	test 3	value 3	needed 3	last 3
1/1/2018	A	A.2	Max	test 4	value 4	needed 4	last 4
1/15/2018	B	B.1	Min	test 5	value 5	needed 5	last 5
1/15/2018	B	B.2	Max	test 6	value 6	needed 6	last 6
1/15/2018	C	C.1	Min	test 7	value 7	needed 7	last 7
1/15/2018	C	C.2	Max	test 8	value 8	needed 8	last 8

Re: Return specific value from straight table

Shubham_Deshmukh — Wed, 06 Mar 2019 13:39:23 GMT

Try this:
=Only(if([Partner Product]='A.1' and [Min/Max]='Min',[Needed values 1]))

Re: Return specific value from straight table

NewToQV — Wed, 06 Mar 2019 21:15:51 GMT

Hey @Shubham_Deshmukh , thanks for your quick response. Looks to be doing exactly what is needed. Thank you!!

One more question. There may be multiple dates for that particular 'Partner Product'. How can I also ask to use the maximum date from the 'As of Date' field in addition?

Re: Return specific value from straight table

Shubham_Deshmukh — Thu, 07 Mar 2019 07:22:47 GMT

Hope you are expecting this,

=MaxString({<[As Of Date]={"=$(=max([As Of Date]))"},[Partner Product]={'A.1'} , [Min/Max]={'Min'}>}[Needed values 1])
//=Only(if([Partner Product]='A.1' and [Min/Max]='Min',[Needed values 1]))[As Of Date]={"=$(=max([As Of Date]))"},

Regards

Re: Return specific value from straight table

NewToQV — Thu, 07 Mar 2019 13:10:03 GMT

Hello @Shubham_Deshmukh . Thanks again. However when using the below I am receiving and 'Error: Error in set modifier expression'.

=MaxString({<[As Of Date]={"=$(=max([As Of Date]))"},[Partner Product]={'A.1'} , [Min/Max]={'Min'}>}[Needed values 1])

I left out the commented out portion, does that need to be included? Thoughts?

Re: Return specific value from straight table

Shubham_Deshmukh — Thu, 07 Mar 2019 13:14:34 GMT

See my exp, working absolutely fine and getting result,please check you square and curly braces once again. Commented line does not have any effect on expression.

Re: Return specific value from straight table

NewToQV — Thu, 07 Mar 2019 13:23:45 GMT

Strange. All I have done is copy and paste your expression and change the names of my fields to what I have. The previous expression without the date is working perfectly.

Re: Return specific value from straight table

Shubham_Deshmukh — Thu, 07 Mar 2019 13:38:22 GMT

Hi ,

Look at my data, this is what I am using and expression is same which I mentioned earlier,

Above data and that expression give result = testOutput - which is I think correct output.

Re: Return specific value from straight table

NewToQV — Thu, 07 Mar 2019 13:53:40 GMT

Based on what I see below, the result 'testOutput' is correct and is exactly what I am looking to do.

I've made sure my expression matches yours and the expression just returns a hyphen '-'

I guess these something wrong with my date format?

I have an excel file which I am loading. Just as you did I placed a dummy row with a new date in the As Of Date for the same partner product. The column 'As Of Date' in the excel file is formatted as a date (m/d/yyyy).

Any other thoughts as to why it would just return a hyphen?

Re: Return specific value from straight table

Shubham_Deshmukh — Thu, 07 Mar 2019 13:57:10 GMT

I changed the date format intentionally, if possible you change your date format and then try.

Re: Return specific value from straight table

NewToQV — Thu, 07 Mar 2019 14:01:00 GMT

I changed the date format in my excel file to match yours 'd-m-yyyy' and uploaded it into QV. I am still receiving a hyphen for my result.

Re: Return specific value from straight table

Shubham_Deshmukh — Thu, 07 Mar 2019 14:08:37 GMT

It's really strange. Doesn't make sense but change date format as per your system's date format.

Re: Return specific value from straight table

NewToQV — Thu, 07 Mar 2019 14:11:27 GMT

I changed it back to match my systems date format and it still receive a hyphen. Looks like I'll just stage and track date changes to another tab 😞 If you can think of anything else please let me know. Thank you for your assistance!

Re: Return specific value from straight table

Shubham_Deshmukh — Thu, 07 Mar 2019 14:13:04 GMT

Sure...