How To match data in two columns and display third coulmn

[#1]

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

[jagan]

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]

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]

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

[#5]

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

[#6 · Accepted solution]

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;

[#7]

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;