Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How To match data in two columns and display third coulmn

I have to get a csv file into Qlikview. Base on two columns values I need to get the third column data.

Col1  Col2  Col3

1        2      100

2         3     200

1        2      500

2         4      600

2          4      500

Only I need to get only red color comuns data. that is(  col1=1 and col2=2) and (Col1=2 and col2= 4) and exclude remaining.


Thanks

Pavan

1 Solution

Accepted Solutions
Not applicable

Re: How To match data in two columns and display third coulmn

Hi Pavan, the best approach is create the temp table with all the possible values you need and use the Where Exists function to load only possible values in the Temp Table like below:

TEMP:

LOAD * INLINE [

KEY

1-2

2-4

];

TABLE:

LOAD Col1, Col2, Col3 from File.csv

Where Exists(KEY, Col1&'-'&Col2);

Drop Table TEMP;

6 Replies
MVP
MVP

Re: How To match data in two columns and display third coulmn

Hi,

Try like this

LOAD

*

FROM Data.csv

WHERE  Col1=1 and Col2=2) OR (Col1=2 and Col2= 4);

Hope this helps you.

Regards,

Jagan.

syukyo_zhu
Contributor III

Re: How To match data in two columns and display third coulmn

hi,

you can try create a new key in your first table

AutoNumberHash128(col1&'|'&col1) as Newkey

and create your second table like this

load *

from table

where col1&'|'&col1='1|2'.

senpradip007
Valued Contributor III

Re: How To match data in two columns and display third coulmn

Try like

T1:

LOAD * Inline [

Col1,  Col2,  Col3
1, 2,    100
2, 3,    200
1,      2,    500
2,      4,    600
2,      4,    500
]
;

T2:
NoConcatenate
LOAD
    
Col1,
    
Col2,
     Col3

Resident T1 Where Col2 = (Col1*2);

Not applicable

Re: How To match data in two columns and display third coulmn

I didnt get how u calculate Col3 data,I attached a sample file,hope from this file you get an idea..

Not applicable

Re: How To match data in two columns and display third coulmn

Hi Pavan, the best approach is create the temp table with all the possible values you need and use the Where Exists function to load only possible values in the Temp Table like below:

TEMP:

LOAD * INLINE [

KEY

1-2

2-4

];

TABLE:

LOAD Col1, Col2, Col3 from File.csv

Where Exists(KEY, Col1&'-'&Col2);

Drop Table TEMP;

Not applicable

Re: How To match data in two columns and display third coulmn

Hi Dathu

the script is not able to recognise the field vaues in the Where Exists funtion. Please let me know the mistake in the following code.


TEMP:
Load * Inline
[
Key
01-01
01-02
02-01
02-02
01-03
01-04
01-05
01-07
01-08
02-03
02-04
02-05
02-07
02-08
01-30
01-31
01-32
01-33
01-50
01-65
02-30
02-31
02-32
02-33
02-65
  ];          

[OGIS Premium]:
Load [@1:3]  as [Rep-Off],
     [@4:6] as [Cur-Cd],
     [@7:8] as Org,
     [@9:10] as [Mgr-Cd],
     [@11:12] as [Maj-Lin],
     [@13:14 ] as [Min-Lin],
     [@15:21] as  [Iss-Comp],
     [@22:23] as   [Prod-Src],
     [@24:25] as  [Tran-Type],
     [@26:27] as [Acct-Ln],
     [@28:30] as [Prod-Off],
     [@31:40] as [Polcy-No],
     [@41:46] as [Cert-No],
     [@47:56] as  [Claim-No],
     [@57:63] as [Acct-Prd],
     [@64:72] as [Plcy-Inc-Dt] ,
     [@73:81] as [Prem-Eff-Dt],
     [@82:90] as [Loc-Edit-Dt],
     [@91:99] as [Accid-Dt],
     [@100:108] as [Prem-Exp-Dt],
     [@109:117] as [Claim-Cre-Dt],
     [@118:118] as [Correct-Cd],
     [@119:128] as [Plcy-Id],
     [@129:129] as [Rsrve-Tp],
     [@130:138] as [Req-No],
     [@139:140] as [Meth-Of-Pay],
     [@141:149] as [Ind-Acct-No],
     [@150:154] as [Catas-No],
     [@155:155]  as [Ho-Rev-Cd],
     [@156:161] as [Prod-No],
     [@162:163] as  [RI-Type],
     [@164:170]  as [RI-Comp],
     [@171:173] as [Cls-Prl],
     [@174:189] as [Vess-Name],
     [@190:197]  as Blank1,
     [@198:201] as  [Cause-Of-Loss],
     [@202:204] as [Inj-Dam],
     [@205:207] as  [Anat-Prop],
     [@208:210] as [Req-Rev-No],
     [@211:214]  as Blank2,
     [@215:220] as [RI-Stat-Id],
     [@221:227] as Blank,
     [@228:230] as [Cntry-Cd] ,
     [@231:247] as [Grp-Curr-Amt],
     [@248:252] as Blank3,
     [@253:259] as [Acqu-Rte-Grp],
     [@260:266]  as [Agy-Comm-Rte-Grp],
     [@267:273] as [Fac-Rte-Grp],
     [@274:274] as [Pay-Tp],
     [@275:276] as [Loc-Br-Anal],
     [@277:281]  as [Lob],
     [@282:284]  as [Loc-Prd-Src],
     [@285:285] as Blank4,
     [@286:291] as [Ins-Stat-Cd1],
     [@292:297] as [Ins-Stat-Cd2],
     [@298:n] as Blank5
    
FROM
(fix, codepage is 1252, no eof)
Where Exists(Key, ActLine & '-' & TransType);
DROP Table TEMP;



Community Browser