Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bharath8
Contributor II
Contributor II

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?

bharath8
Contributor II
Contributor II
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!!

bharath8
Contributor II
Contributor II
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

];

bharath8
Contributor II
Contributor II
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 

bharath8
Contributor II
Contributor II
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

];

View solution in original post

bharath8
Contributor II
Contributor II
Author

Thanks a lot for your help!