Unique Codes to be displayed

[dmohanty]

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

[dmohanty]

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

[swuehl · Accepted solution]

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;

[dmohanty]

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

[dmohanty]

Hi Swuehl,

That logic provided by you, works perfectly...Understood the logic. Many Thanks!!!!