Comparing String

[mansi_dv]

Hi All,

I have 2 columns namely CityA and CityZ in the qvd.

They have values like AHMD, MUMB, CHNI, BNGR etc.

But I need to compare the cities, if CityA is numerically higher than the CityZ, the swap the values.

For example,

CityA                    CityZ

--------------------------------

AHMD                    MUMB

CHNI                     BNGR

I need to check if CHNI is numerically higher than BNGR, then CityA will be BNGR and CityZ will be CHNI.

Also there is one more column called Area for each City column.

Example,

CityA                   AreaA                          CityZ                         AreaZ

-----------------------------------------------------------------------------------

AHMD                   S V Road                   MUMB                     Marg

CHNI                    G D Road                  BNGR                      A Road

So if we swap the citynames, that particular area name should also get swapped.

Please help me out with this.

[Gysbert_Wassenaar · Accepted solution]

What do you mean 'numerically higher'? Text values are not numbers so the cannot be numerically anything. If you simply want to compare the alphabetic rankings then try: if( CityA > CityZ, CityA, CityZ).

[alexandros17]

You have something like :

Load CityA, AreaA,  CityZ, AreaZ from xxx.qvd

transform it in:

Load

     If(CityA > CityZ, CityZ, CityA) as CityA,

     If(CityA > CityZ, AreaZ, AreaA) as AreaA

     If(CityA > CityZ, CityA, CityZ) as CityZ,

     If(CityA > CityZ, AreaA, AreaZ) as AreaZ

     .....

from xxx.qvd

Let me know

[mansi_dv]

Thanks Gysbert. But what about the swapping of the related column?

[Gysbert_Wassenaar]

if( CityA > CityZ, RegionA, RegionZ).