topic Re: Data Load in QlikView

Data Load

upaliwije — Tue, 08 Nov 2016 06:47:05 GMT

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

Re: Data Load

tresB — Tue, 08 Nov 2016 07:16:28 GMT

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;

Re: Data Load

hemanthaanichet — Tue, 08 Nov 2016 07:16:36 GMT

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

Re: Data Load

maleksafa — Tue, 08 Nov 2016 07:16:57 GMT

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.

Re: Data Load

its_anandrjs — Tue, 08 Nov 2016 07:23:06 GMT

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

Re: Data Load

MindaugasBacius — Tue, 08 Nov 2016 07:30:34 GMT

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