Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data in my qvd table in follwing format
POLICY_NO | TYPE | AMOUNT |
151A0002966 | F | -2,269 |
151A0002867 | R | 40,928 |
151A0002895 | F | -59,080 |
151A0002895 | R | 59,080 |
141C0002351 | R | 33,850 |
141A0002653 | F | -20,514 |
141A0002653 | R | 20,514 |
151A0002900 | F | -27,279 |
151A0002900 | R | 27,279 |
151A0002889 | R | 34,077 |
151D0001342 | R | 178,594 |
161A0003154 | F | -28,961 |
151A0003098 | F | -45,304 |
141C0002357 | R | 31,695 |
151C0002471 | F | -20,294 |
151A0002858 | F | -33,549 |
151A0002858 | R | 33,549 |
0101B000880 | R | 8,990 |
151B0001207 | F | -58,328 |
151B0001207 | R | 58,328 |
151A0002942 | F | -51,298 |
151A0002942 | R | 59,630 |
151A0002937 | F | -22,781 |
151A0002937 | R | 93,561 |
151A0002936 | F | -70,745 |
151A0002936 | R | 70,745 |
151A0002934 | R | 53,753 |
0101C001869 | R | 21,253 |
151A0002970 | F | -9,280 |
151A0002952 | F | -37,015 |
151A0002952 | R | 37,015 |
In my load script I want to restrict data load only for the records where following condtions meet
If type = F and amount<0 such records should be loaded, and corrosponding exact positive value too should be loaded and the type shoild be t and policy no should be the same . In other words Records hilighted in red should only be loaded. Pls help me to write the script
One way could be:
Raw:
Load * Inline [
POLICY_NO,TYPE,AMOUNT
151A0002966,F,-2269
151A0002867,R,40928
151A0002895,F,-59080
151A0002895,R,59080
141C0002351,R,33850
141A0002653,F,-20514
141A0002653,R,20514
151A0002900,F,-27279
151A0002900,R,27279
151A0002889,R,34077
151D0001342,R,178594
161A0003154,F,-28961
151A0003098,F,-45304
141C0002357,R,31695
151C0002471,F,-20294
151A0002858,F,-33549
151A0002858,R,33549
0101B000880,R,8990
151B0001207,F,-58328
151B0001207,R,58328
151A0002942,F,-51298
151A0002942,R,59630
151A0002937,F,-22781
151A0002937,R,93561
151A0002936,F,-70745
151A0002936,R,70745
151A0002934,R,53753
0101C001869,R,21253
151A0002970,F,-9280
151A0002952,F,-37015
151A0002952,R,37015
];
Temp:
Load
*
Where Summed=0;
Load Sum(AMOUNT) as Summed,
POLICY_NO as PLNO
Resident Raw Group By POLICY_NO;
Final:
NoConcatenate
Load
*
Resident Raw where Exists(PLNO,POLICY_NO);
DROP table Temp, Raw;
One way could be:
Raw:
Load * Inline [
POLICY_NO,TYPE,AMOUNT
151A0002966,F,-2269
151A0002867,R,40928
151A0002895,F,-59080
151A0002895,R,59080
141C0002351,R,33850
141A0002653,F,-20514
141A0002653,R,20514
151A0002900,F,-27279
151A0002900,R,27279
151A0002889,R,34077
151D0001342,R,178594
161A0003154,F,-28961
151A0003098,F,-45304
141C0002357,R,31695
151C0002471,F,-20294
151A0002858,F,-33549
151A0002858,R,33549
0101B000880,R,8990
151B0001207,F,-58328
151B0001207,R,58328
151A0002942,F,-51298
151A0002942,R,59630
151A0002937,F,-22781
151A0002937,R,93561
151A0002936,F,-70745
151A0002936,R,70745
151A0002934,R,53753
0101C001869,R,21253
151A0002970,F,-9280
151A0002952,F,-37015
151A0002952,R,37015
];
Temp:
Load
*
Where Summed=0;
Load Sum(AMOUNT) as Summed,
POLICY_NO as PLNO
Resident Raw Group By POLICY_NO;
Final:
NoConcatenate
Load
*
Resident Raw where Exists(PLNO,POLICY_NO);
DROP table Temp, Raw;
Hi Upal,
expression as follow
if(type = F ,amount<0, amount) these is the expression u need i think.
But i have doubt in your requirement
1. both the policy num are same ,whether you need to change the type code too
2. if u need type F only obviously you wont get positive values
if have any sample data let get share that would be more helpful to you
Regards
Hemanth
on the top of my mind now i suggest the following:
load the data first in a resident table for the values > 0 and type F.
Load * Resident Policy where Type = 'F' and amount > 0.
then load another table where not exist(Policy_No,Amount) to exclude the data loaded in the first table, however for this load use absolute value for the amounts (fabs).
which means that now the second table has all the values except the data loaded in the first step, and they are all positive figures.
link the two tables together at the end using your key field and only the amounts matching will be linked, however you need to make sure to reverse back the amounts from the second table.
Hi,
Try this ways first load the data by filter the value and then again concatenate values by the table but be insure the values in the data base is exactly the same.
Data:
LOAD POLICY_NO, TYPE, AMOUNT;
LOAD * Inline [
POLICY_NO, TYPE, AMOUNT
151A0002966, F, -2269
151A0002867, R, 40928
151A0002895, F, -59080
151A0002895, R, 59080
141C0002351, R, 33850
141A0002653, F, -20514
141A0002653, R, 20514
151A0002900, F, -27279
151A0002900, R, 27279
151A0002889, R, 34077
151D0001342, R, 178594
161A0003154, F, -28961
151A0003098, F, -45304
141C0002357, R, 31695
151C0002471, F, -20294
151A0002858, F, -33549
151A0002858, R, 33549
0101B000880, R, 8990
151B0001207, F, -58328
151B0001207, R, 58328
151A0002942, F, -51298
151A0002942, R, 59630
151A0002937, F, -22781
151A0002937, R, 93561
151A0002936, F, -70745
151A0002936, R, 70745
151A0002934, R, 53753
0101C001869, R, 21253
151A0002970, F, -9280
151A0002952, F, -37015
151A0002952, R, 37015
];
NoConcatenate
New1:
LOAD
*
Resident Data
Where TYPE = 'F';
DROP Table Data;
Concatenate(New1)
LOAD
POLICY_NO,
'R' as TYPE,
AMOUNT * -1 as AMOUNT
Resident New1;
Regards
Anand
Maybe this could help you out:
Table:
LOAD POLICY_NO,
TYPE,
AMOUNT
FROM
[https://community.qlik.com/thread/239003]
(html, codepage is 1257, embedded labels, table is @1);
Left Join (Table)
LOAD POLICY_NO,
Sum(AMOUNT) as Sum
FROM
[https://community.qlik.com/thread/239003]
(html, codepage is 1257, embedded labels, table is @1)
Group By POLICY_NO
;
NoConcatenate
tmp:
LOAD POLICY_NO,
TYPE,
AMOUNT
Resident Table
Where Sum = 0;
DROP Table Table;
RENAME Table tmp to Table;
Result:
I am quite sure that there should be a more efficient way than mine