Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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'.
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);
I didnt get how u calculate Col3 data,I attached a sample file,hope from this file you get an idea..
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;
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;