topic Re: filter table data in QlikView

filter table data

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

Hi There,

I have 2 tables(script below) and both table can link by 4 fields (ie. Product, system, location, businessarea). However I don't want to create composite key as you can see there is only 1 common row between 2 tables based on composite key and I don't want to loose 'Problems table' data in UI.

So instead of creating composite key, I concatenated both tables, so I can filter the data of each field individually for both table( I mean if a user select Product-SAP, they should still able to filter SAP row in problems table.

My main requirement is, I don't want to show Problems data in UI objects that are specific to Sales sheet.

Eg- The product listbox shouldn't show Product-ZZ value. Similarly the System listbox shouldn't show PeopleSoft & Microsoft.

To achieve this I created Dataset field, but it is not working.

Please help!

Sales:
LOAD *,
'Sales' as DataSet
INLINE
[
Prouct, System, Location, BusinessArea, Sales
AA, SAP, DE, Manufacturing, 67
AA, Oracle, US, IT, 80
BB, SAP, UK, Accounting, 578
CC, IBM, IN, IT, 79
DD, MS, US, IT, 969
DD, IBM, US, Research, 89
];

Concatenate
Problems:
LOAD *,
'Problems' as DataSet
INLINE [
P_ID, Prouct, System, Location, BusinessArea
200, AA, PeopleSoft, US, Manufacturing
300, BB, SAP, UK, IT
400, CC, IBM, IN, IT
500, ZZ, Microsoft, FR, HR
];

Re: filter table data

sunny_talwar — Mon, 12 Jun 2017 11:40:35 GMT

What is the required output?

Re: filter table data

Anonymous — Mon, 12 Jun 2017 12:30:01 GMT

Hi Sunny,

I am mainly interested in Sales data and its matching Problems data.

I want all data of Sales table and also matching data of Problems table(not all data of problems table) in each listbox, so I can filter data.

eg-

Product

but not ZZ.

The below expression used in listbox is filtering out all Problems data, which is not what I want.

=if(DataSet = 'Sales',Prouct)

Please use my attached app if you want.

Thanks!!

Re: filter table data

Anonymous — Mon, 12 Jun 2017 13:30:01 GMT

I think Sunny went to bed.

Is there anyone who can help me.

Re: filter table data

avinashelite — Mon, 12 Jun 2017 13:42:54 GMT

Check the attachment for the solution

Sales:

LOAD *,

'Sales' as DataSet,

1 as Exist_Flag

INLINE

[

Prouct, System, Location, BusinessArea, Sales

AA, SAP, DE, Manufacturing, 67

AA, Oracle, US, IT, 80

BB, SAP, UK, Accounting, 578

CC, IBM, IN, IT, 79

DD, MS, US, IT, 969

DD, IBM, US, Research, 89

];

Concatenate

Problems:

LOAD *,

'Problems' as DataSet ,

if(Exists(Prouct,Prouct)=-1,1,0) as Exist_Flag

INLINE [

P_ID, Prouct, System, Location, BusinessArea

200, AA, PeopleSoft, US, Manufacturing

300, BB, SAP, UK, IT

400, CC, IBM, IN, IT

500, ZZ, Microsoft, FR, HR

];

Re: filter table data

Anonymous — Mon, 12 Jun 2017 13:55:20 GMT

Thanks Avinash.

This is working only for Product. The Exist_Flag is not working for other fields like- BusinessArea, Location, System.

I am thinking to have a single flag, if possible.

Re: filter table data

avinashelite — Mon, 12 Jun 2017 14:01:45 GMT

IF you want a single flag then it would on the combination of all eg:

AA, SAP, DE, Manufacturing

and

AA, PeopleSoft, US, Manufacturing

this won't get mapped as exists s from sales to Problems in this case you need to create individual flags

Re: filter table data

Anonymous — Mon, 12 Jun 2017 14:04:04 GMT

ok, Please help me with individual flags.

Re: filter table data

avinashelite — Mon, 12 Jun 2017 14:14:56 GMT

Sales:

LOAD *,

'Sales' as DataSet,

1 as Product_Exist_Flag,

1 as System_Exist_Flag,

1 as Location_Exist_Flag,

1 as BusinessArea_Exist_Flag

INLINE

[

Prouct, System, Location, BusinessArea, Sales

AA, SAP, DE, Manufacturing, 67

AA, Oracle, US, IT, 80

BB, SAP, UK, Accounting, 578

CC, IBM, IN, IT, 79

DD, MS, US, IT, 969

DD, IBM, US, Research, 89

];

Concatenate

Problems:

LOAD *,

'Problems' as DataSet ,

if(Exists(Prouct,Prouct)=-1,1,0) as Product_Exist_Flag,

if(Exists(System,System)=-1,1,0) as System_Exist_Flag,

if(Exists(Location,Location)=-1,1,0) as Location_Exist_Flag,

if(Exists(BusinessArea,BusinessArea)=-1,1,0) as BusinessArea_Exist_Flag

INLINE [

P_ID, Prouct, System, Location, BusinessArea

200, AA, PeopleSoft, US, Manufacturing

300, BB, SAP, UK, IT

400, CC, IBM, IN, IT

500, ZZ, Microsoft, FR, HR

];

Re: filter table data

Anonymous — Mon, 12 Jun 2017 14:24:11 GMT

Thanks a lot for your help!

Re: filter table data

avinashelite — Mon, 12 Jun 2017 14:28:27 GMT

Glad to help you