Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

filter table data

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
]
;

 

1 Solution

Accepted Solutions
avinashelite

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

];

View solution in original post

10 Replies
sunny_talwar

What is the required output?

Anonymous
Not applicable
Author

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

AA

BB

CC

DD

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!!

Anonymous
Not applicable
Author

I think Sunny went to bed.

Is there anyone who can help me.

avinashelite

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

];

Anonymous
Not applicable
Author

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.

avinashelite

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 

Anonymous
Not applicable
Author

ok, Please help me with individual flags.

avinashelite

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

];

Anonymous
Not applicable
Author

Thanks a lot for your help!