combine the two fields into one

[ravindraa]

Hi all,

  I have one requirement that is we have one database in that one table(CONTRACT TABLE) have below fields

status

contract

customer

from this table we are getting CONTRACT AND CUSTOMER values straight way but where as STATUS maintains integers(1,4,5,8).

to get the STATUS value(AS STRING) we need to join the some other table (STATUS TABLE) in status table maintains below fields

index

Constraint ID

Display Name

to get status value as(ACTIVE , CANCELLED , CREATED, NA) like, we have gathered some code from DB team they are using like below.

CONTRACT TABLE is the furly database side i mean we don't understand those field name so, to understand the fields they have used another table(STATUS TABLE) that is maintain the

STATUS TABLE as STATUS TABLE1 ON CONTRACT TABLE.status = STATUS TABLE1.nIndex ANDSTATUS TABLE1.nConstraintID= 10760

STATUS TABLE1.tdisplayname

STATUS TABLE1.tDisplayName as nContractStatus.

if any one know how to get this type of logic in Qlikview please give me suggestion it is very urgent. please do the need full.

[#2]

Hi,

Below code will work.Have you tried this?

Contract Table:

Load

status,

contract,

customer

From ***********;

Status Table:

Load

index as status,

Constraint ID,

Display Name

From *******

Where [Constraint ID] = '10760' ;

Regards,

Ravikumar

[ravindraa]

Hi Ravikumar,

  i attaching my code please give me suggestion  on this.

statustable:

      LOAD FldNumeric3003481 as status,

        nID as contract;

      SQL SELECT FldNumeric3003481,

        nID

      FROM ********."tblObjectType5005_2";

join

status:

      LOAD nConstraintID,

           nIndex as status,

         tDisplayName  as userdisplaystatus    Where FldNumeric3003481=nIndex  and nConstraintID = 10760 ;

      SQL SELECT nConstraintID,

          nIndex,

          tDisplayName

      FROM *********.dbo.tblEnum ;

this is i wrote in my application .

[#4]

Hi,

try the below code.

Contract Table:

      LOAD FldNumeric3003481 as status,    

                nID as contract;

      SQL SELECT FldNumeric3003481,

                           nID

      FROM MHRILMain.dbo."tblObjectType5005_2"; 

Right Join                //Use appropriate Join

Status Table:

      LOAD nConstraintID,

                nIndex as status,       

                tDisplayName  as userdisplaystatus

                ;       

      SQL SELECT nConstraintID,

                           nIndex,        

                           tDisplayName

      FROM MHRILMain.dbo.tblEnum

                 Where nConstraintID = '10760' ;

Note: You can load without join also.Association will exist between two tables since same column name (status).

Regards,

Ravikumar

[ravindraa]

Hi ravikumar,

  it is working fine but i have used the where condition and exits().

thank you.