Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
You can use WHERE NOT EXISTS():
LOAD
Year,
Value
FROM A.xls (...);
LOAD
Year,
Value
FROM B.xls (...)
WHERE NOT EXISTS(Year);
you can use inner join which will give all the records once.
Regards,
Himanshi
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);
>>WHERE NOT EXISTS(Year);
Won't this load only the first line with Year, because after that the Year value does exist?
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;
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);
replace my load inline with your excel load
A:
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;
thanks a lot for you help!!