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.

[whiteline]

Hi.

QlikView makes the references between tables using field names.

You can just choose the same name for the fields 'status' and 'index'.

[sujeetsingh]

Hi,

If the values of two tables are such that can be used as Key then just load two tables separate and keep the Status name field as same the linking will be done by Qlikview .

[sushil353]

Hi,

Try below code

Contract:

load

status

contract

customer

from source;

status tabe:

load

index as status

Constraint ID

Display Name

from source;

HTH

sushil

[ravindraa]

Hi whiteline ,

   To get the Display name need to compare the  'status' and 'index' as well as nConstraintID=10760 once it is satisfy the tDisplayName will be get the value, please give me suggestion how to get the value.

Thanks & Regards

Ravindraa

[its_anandrjs]

You can try like below load script and rename the fields index as status and put where condition

Tab1:

Load

status,

contract,

customer

From Location;

Tab2:

Load

index,

index as status,

Constraint ID,

Display Name

From Location Where [Constraint ID] = 10760'

[#7]

Hi,

You can try the below option as well.

Map1:

Mapping Load * Inline [

status, statusname

1, ACTIVE

4, CANCELLED

5, CREATED

8, NA

];

Contract Table:

LOAD

status,

ApplyMap('Map1',status) as statusname,

contract,

customer

FROM *******;

Regards,

Ravikumar

[ravindraa]

Hi Sushil Kumar,

  here i need to compare the STATUS and INDEX as well as nConstraintID= 10760 when these value is equal this information is available in tDisplayName ,because  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

index

Constraint ID

Display Name fields

To display the status the condition should be satisfy then only the value will be come

[ravindraa]

Hi sujeetsingh,

  here CONTRACT TABLE maintain the DB value(these value doesn't understand users ) ,STATUS TABLE maintains the

user understand value to get STATUS TABLE value i need to write the join between tables like below

this is DB side people wrote the code to get the data

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

STATUS TABLE1.tdisplayname

STATUS TABLE1.tDisplayName as nContractStatus.

like wise i have tried but i am getting all tDisplayName values i should get only join condition satisfy values only.(i mean ACTIVE , CANCELLED , CREATED),

[ravindraa]

like wise i have tried but i am getting all tDisplayName values i should get only join condition satisfy values only.(i mean ACTIVE , CANCELLED , CREATED),