Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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
];
What is the required output?
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!!
I think Sunny went to bed.
Is there anyone who can help me.
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
];
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.
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
ok, Please help me with individual flags.
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
];
Thanks a lot for your help!