topic Re: Data Redundancy in QlikView

Data Redundancy

Mon, 30 Nov 2015 07:09:27 GMT

Hi guys,

I have a excel file A which contains 2015 and 2014 data.

Also, I have another excel file B which contains 2016 and 2015 data.

A .qvd file is generated by appending B to A.

As you might have guessed 2015 data is loaded twice.

how do I rectify this issue.

thanks in advance.

Re: Data Redundancy

swuehl — Mon, 30 Nov 2015 10:29:41 GMT

You can use WHERE NOT EXISTS():

LOAD

Year,

Value

FROM A.xls (...);

LOAD

Year,

Value

FROM B.xls (...)

WHERE NOT EXISTS(Year);

Re: Data Redundancy

himanshi — Mon, 30 Nov 2015 11:56:05 GMT

you can use inner join which will give all the records once.

Regards,

Himanshi

Re: Data Redundancy

Anonymous — Mon, 30 Nov 2015 12:01:41 GMT

you may use like:

A_Excel:

Load

Year,

Value

From A.Excel;

let VMaxYear=max(Year);

B_Excel:

Load

From B.Excel where Year>$(VMaxYear);

Re: Data Redundancy

jonathandienst — Mon, 30 Nov 2015 12:10:43 GMT

>>WHERE NOT EXISTS(Year);

Won't this load only the first line with Year, because after that the Year value does exist?

Re: Data Redundancy

jonathandienst — Mon, 30 Nov 2015 12:12:15 GMT

You might want to do it like this:

LOAD

Year,

Year As LoadedYear,

Value

FROM A.xls (...);

LOAD

Year,

Value

FROM B.xls (...)

WHERE NOT EXISTS(LoadedYear, Year);

DROP Field LoadedYear;

Re: Data Redundancy

swuehl — Mon, 30 Nov 2015 12:16:17 GMT

That's correct, if multiple lines for same Year exist.

Maybe it's better not going for the Year field, but for a primary key, something that identifies the record as being redundant (can't tell from the OP description).

LOAD

Key,

Year,

Value

FROM A.xls (...);

LOAD

Key,

Year,

Value

FROM B.xls (...)

WHERE NOT EXISTS(Key);

Re: Data Redundancy

maxgro — Mon, 30 Nov 2015 13:04:29 GMT

replace my load inline with your excel load

load year, value, year as year2 inline [

year, value

2014,1

2014,2

2015,1

2015,2

];

Concatenate (A)

load * inline [

year, value

2016,1

2016,2

2015,1

2015,2

]

Where not Exists(year2, year)

;

DROP Field year2;

Re: Data Redundancy

Mon, 30 Nov 2015 13:36:36 GMT

thanks a lot for you help!!