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

How To match data in two columns and display third coulmn

Tue, 19 Aug 2014 08:54:28 GMT

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

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

jagan — Tue, 19 Aug 2014 09:06:01 GMT

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.

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

syukyo_zhu — Tue, 19 Aug 2014 09:10:24 GMT

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'.

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

senpradip007 — Tue, 19 Aug 2014 09:12:00 GMT

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

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

Tue, 19 Aug 2014 09:41:17 GMT

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

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

Tue, 19 Aug 2014 13:23:26 GMT

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;

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

Tue, 09 Sep 2014 13:19:32 GMT

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;