Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need a quick and effective help in my scenario below:
I have a table called Supplier.qvd (or Supplier table from database) which has fields: REGION, CODE,LOC,NAME. In Region I have: IND, USA. Suppose I have a CODE called 100 and LOC: 01 which is available for both the Regions and they have different NAME (ABC and XYZ respectively). Now the data looks like this:
REGION | CODE | LOC | NAME |
IND | 100 | 01 | ABC |
USA | 100 | 01 | XYZ |
My GUI looks like this:
I have a selector(List Box) for Region. I have one more list box which is calculated as (may be at GUI level or script level): CODE&'-'&LOC&' '&NAME and the values would look like this:
CODE_LOC_NAME |
100-01 ABC |
100-01 XYZ |
My Requirement is: The CODE_LOC value to be unique and the NAME should come from other Region.
For example, if I select or dont select IND/USA, my CODE_LOC value to always to be unique (not repeated like above), like this:
CODE_LOC_NAME |
100-1 XYZ |
I understand that if I select IND as region, the value would come as 100-01 ABC, but I dont need this value. My CODE_LOC should not be repeated in List Box.
How this can be achieved in QVD/Modeling level or List Box level? Please help on this.
Regards!!!
Not really sure I've understood what you are trying to achieve, especially
'My Requirement is: The CODE_LOC value to be unique and the NAME should come from other Region.'
But maybe this matches your requirement:
Region:
LOAD * INLINE [
REGION, CODE,LOC,NAME
IND, 100, 1, ABC
USA, 100, 1, XYZ
IND, 200, 2, AAA
USA, 300, 2, ZZZ
];
LEFT JOIN (Region) LOAD
CODE,
LOC,
CODE&'-'&LOC&' '&LastValue(NAME) as Code_Loc_Name
Resident Region group by CODE,LOC;
Adding more to above,
If the CODE and LOC are unique(different) for different REGION, it would show up the respective value. Data is like this:
REGION | CODE | LOC | NAME |
IND | 100 | 1 | ABC |
USA | 100 | 1 | XYZ |
IND | 200 | 2 | AAA |
USA | 300 | 2 | ZZZ |
List Box would look like this now:
CODE_LOC_NAME |
100-1 XYZ |
200-2 AAA |
300-2 ZZZ |
Not really sure I've understood what you are trying to achieve, especially
'My Requirement is: The CODE_LOC value to be unique and the NAME should come from other Region.'
But maybe this matches your requirement:
Region:
LOAD * INLINE [
REGION, CODE,LOC,NAME
IND, 100, 1, ABC
USA, 100, 1, XYZ
IND, 200, 2, AAA
USA, 300, 2, ZZZ
];
LEFT JOIN (Region) LOAD
CODE,
LOC,
CODE&'-'&LOC&' '&LastValue(NAME) as Code_Loc_Name
Resident Region group by CODE,LOC;
Hi Swuehl,
Thanks for the quick help, Seems it has started working, I am doing more of Unit Testing now, will let you know.
By this line: 'My Requirement is: The CODE_LOC value to be unique and the NAME should come from other Region.' I meant to say that, if the CODE is same for both the regions where the NAMEs are different, then it should show the single value in my List Box(NAME from the 1st/2nd REGION), like this:
My Table Data
REGION | CODE | LOC | NAME |
IND | 100 | 1 | ABC |
USA | 100 | 1 | XYZ |
IND | 200 | 2 | AAA |
USA | 300 | 2 | ZZZ |
List Box would look like this now:
CODE_LOC_NAME |
100-1 XYZ |
200-2 AAA |
300-2 ZZZ |
Regards!!!
Hi Swuehl,
That logic provided by you, works perfectly...Understood the logic. Many Thanks!!!!