Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Data Load

Hi,

I have data in my qvd table in follwing format

POLICY_NOTYPEAMOUNT
151A0002966F-2,269
151A0002867R40,928
151A0002895F-59,080
151A0002895R59,080
141C0002351R33,850
141A0002653F-20,514
141A0002653R20,514
151A0002900F-27,279
151A0002900R27,279
151A0002889R34,077
151D0001342R178,594
161A0003154F-28,961
151A0003098F-45,304
141C0002357R31,695
151C0002471F-20,294
151A0002858F-33,549
151A0002858R33,549
0101B000880R8,990
151B0001207F-58,328
151B0001207R58,328
151A0002942F-51,298
151A0002942R59,630
151A0002937F-22,781
151A0002937R93,561
151A0002936F-70,745
151A0002936R70,745
151A0002934R53,753
0101C001869R21,253
151A0002970F-9,280
151A0002952F-37,015
151A0002952R37,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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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;

Capture.PNG

View solution in original post

5 Replies
tresesco
MVP
MVP

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;

Capture.PNG

hemanthaanichet
Creator III
Creator III

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

maleksafa
Specialist
Specialist

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.

its_anandrjs

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

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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:

Screenshot_1.jpg

I am quite sure that there should be a more efficient way than mine