topic Re: Search all selected value in two fields and display the resultant value in QlikView

Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 03:36:15 GMT

Hi All,

I am having two tables Customers and Relations.

I have Cust_Id in customer table.

IP_ID_Num

100

200

300

I have Cust_Id and Relation ID in Relations table.

IP_ID_Num	PRSL
100	200
100	300
200	400
300	200

Association between these two tables is IP_ID_Num.

When we select 200 from IP_ID_NUM, I get to see 400 from PRSL.

But my requirement is when we select a value from IP_ID_NUM, I need to search for that value in both IP_ID_NUM and PRSL fields.

When I select 200 from IP_ID_NUM, I need to see two rows.

1. IP_ID_NUM - 100 and PRSL - 200. (Because PRSL value is 200)

2. P_ID_NUM - 200 and PRSL - 400. (IP_ID_Num value is 200).

Can anyone please help me?

Thanks in advance!

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:29:47 GMT

Hi,

As you mentioned -

When I select 200 from IP_ID_NUM, I need to see two rows.

1. IP_ID_NUM - 100 and PRSL - 200. (Because PRSL value is 200)

2. P_ID_NUM - 200 and PRSL - 400. (IP_ID_Num value is 200).

I feel it should select

3. P_ID_NUM - 300 and PRSL - 200. (Because PRSL value is 200).

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:31:22 GMT

See if this can help. I needed ID field in Relation table -

Re: Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 04:36:12 GMT

Hi Digvijay,

Sorry, it should select 3 values. I need to see all the values of IP_ID_NUM where IP_ID_NUM or PRSL is 200.

1. IP_ID_NUM - 100 and PRSL - 200. (Because PRSL value is 200)

2. IP_ID_NUM - 200 and PRSL - 400. (IP_ID_Num value is 200).

3. IP_ID_NUM - 300 and PRSL - 200. (Because PRSL value is 200).

Is there a way to achieve?

Re: Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 04:41:35 GMT

Did you try to attach any solution ?

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:42:34 GMT

Ya, I can see it attached? Are you not able to see QVW attached?

Re: Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 04:43:18 GMT

No I cannot. Can you please attach again?

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:44:48 GMT

Attaching qvw once again.

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:47:21 GMT

Now? I needed one extra ID field in relation table as Customer Id is repeating in relation table.

Re: Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 04:49:55 GMT

I don't see it. Can you please explain me how you did it?

Re: Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 04:50:58 GMT

I don't see any attachment

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:54:52 GMT

Script -

Customer:

LOAD IP_ID_Num

FROM

[..\..\..\..\..\..\Downloads\Customer Relation.xlsx]

(ooxml, embedded labels, table is Customers);

CustRel:

LOAD

RowNo() as RelID, //Created ID field

IP_ID_Num,

PRSL

FROM

[..\..\..\..\..\..\Downloads\Customer Relation.xlsx]

(ooxml, embedded labels, table is Relation);

Re: Search all selected value in two fields and display the resultant value

varunreddy — Wed, 21 Feb 2018 04:55:16 GMT

Found it.

Thanks for your help

Cheers,

Varun

Re: Search all selected value in two fields and display the resultant value

Digvijay_Singh — Wed, 21 Feb 2018 04:55:47 GMT

Expression is -

Sum({<RelID=P(RelID)>+1<PRSL={$(=Only(IP_ID_Num))}>}PRSL)