topic Re: combine the two fields into one in QlikView

combine the two fields into one

ravindraa — Thu, 19 Jun 2014 05:56:41 GMT

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.

Re: combine the two fields into one

whiteline — Thu, 19 Jun 2014 06:04:54 GMT

Hi.

QlikView makes the references between tables using field names.

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

Re: combine the two fields into one

sujeetsingh — Thu, 19 Jun 2014 06:07:33 GMT

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 .

Re: combine the two fields into one

sushil353 — Thu, 19 Jun 2014 06:08:14 GMT

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

Re: combine the two fields into one

ravindraa — Thu, 19 Jun 2014 06:14:33 GMT

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

Re: combine the two fields into one

its_anandrjs — Thu, 19 Jun 2014 06:23:13 GMT

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'

Re: combine the two fields into one

Thu, 19 Jun 2014 06:24:01 GMT

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

Re: combine the two fields into one

ravindraa — Thu, 19 Jun 2014 06:25:12 GMT

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

Re: combine the two fields into one

ravindraa — Thu, 19 Jun 2014 07:37:02 GMT

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),

Re: combine the two fields into one

ravindraa — Thu, 19 Jun 2014 07:40:59 GMT

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),

Re: Re: combine the two fields into one

Thu, 19 Jun 2014 08:24:30 GMT

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

Re: combine the two fields into one

ravindraa — Thu, 19 Jun 2014 09:23:51 GMT

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 .

Re: Re: combine the two fields into one

Thu, 19 Jun 2014 09:44:39 GMT

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

Re: combine the two fields into one

ravindraa — Thu, 19 Jun 2014 09:53:10 GMT

Hi ravikumar,

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

thank you.