topic Re: Find a specific Field Record combination in table in QlikView

Find a specific Field Record combination in table

djbloiss — Wed, 25 Nov 2020 16:16:04 GMT

In an expression I have a unique value and I need to pull a specific field value from a table. How can I do this? I tried looking at the peek function (I know peek can only be used in the script) and at the previous function but I cannot figure it out? If I cannot do it in an expression I am willing to do it in a script and save it in a variable.

Re: Find a specific Field Record combination in table

YoussefBelloum — Tue, 09 Jan 2018 15:17:26 GMT

Hi,

can you add random data with an example ?

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 15:22:53 GMT

Unique Value (not the key though)	Return Value
UV1	10
UV2	20
UV3	30
UV4	40
UV5	50
UV6	60
UV7	70
UV8	80

I have UV3 and I want to return 30.

Re: Find a specific Field Record combination in table

sunny_talwar — Tue, 09 Jan 2018 15:26:05 GMT

May be this

Only({<[Unique Value] = {'UV3'}>} [Return Value])

Re: Find a specific Field Record combination in table

YoussefBelloum — Tue, 09 Jan 2018 15:26:24 GMT

what do you want to do with this ?

show only one row with UV3 and 30 ?

filter the table from the script ?

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 15:30:23 GMT

sunny but how will I tell it what table to pull that from?

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 15:30:38 GMT

I need to use it in set analysis.

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 15:35:05 GMT

btw when I say table I mean I need to pull it from a data table, not a straight table or pivot table.

Re: Find a specific Field Record combination in table

sunny_talwar — Tue, 09 Jan 2018 15:54:48 GMT

Is this data in some field? I mean is UV3 a field value and is 30 a field value or an expression's output?

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 16:00:19 GMT

What I am specifically doing is trying to use Today's date to find a specific week_count in my CalendarTable and then use that in set analysis to filter results.

Remember I am new and I am trying to think of solutions. so I have been searching and using a lookup value in my script to set a variable seems like it might work. Do you think that would be a good solution?

Re: Find a specific Field Record combination in table

sunny_talwar — Tue, 09 Jan 2018 16:03:00 GMT

Would you be able to share few rows or data or an image of how your data looks like?

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 18:42:19 GMT

Sunny,

I tried this and while it is not crashing there is no variable being created.

let vCurrWeekCount = Lookup('Week_Count', 'Transaction Date', Date(Now()), 'MultiCalendar');

the syntax looks correct and those are the correct field names and table name.

Re: Find a specific Field Record combination in table

sunny_talwar — Tue, 09 Jan 2018 18:44:26 GMT

I won't know until I play around with it... would you be able to share a sample?

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 18:48:52 GMT

use the multicalender tab

Re: Find a specific Field Record combination in table

djbloiss — Tue, 09 Jan 2018 18:49:27 GMT

sorry forgot to add the file

Re: Find a specific Field Record combination in table

sunny_talwar — Tue, 09 Jan 2018 18:55:34 GMT

This is one way to do it in the script

Table:

LOAD DateKey,

[Transaction Date],

[Week Count]

FROM

[..\..\Downloads\SampleData (1).xlsx]

(ooxml, embedded labels, table is MultiCalendar);

Temp:

LOAD [Week Count]

Resident Table

Where [Transaction Date] = Today();

LET vCurrWeekCount = Peek('Week Count');

DROP Table Temp;