Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'd like to load only one row if it has same Project Code. That one row can be randomly picked from multiple rows. Is this something that can be easily done? Thanks. Sample QVW and source file are attached.
This one is slightly better
DISTINCT:
LOAD distinct ProjectCode,
Project,
Portfolio,
Program
FROM
DISTINCT.xlsx
(ooxml, embedded labels, table is Sheet1);
Final_DISTINCT:
NoConcatenate
LOAD *
Resident DISTINCT
Where ProjectCode <> Previous(ProjectCode)
Order By ProjectCode;
DROP Table DISTINCT;
May be this
DISTINCT:
LOAD distinct ProjectCode,
Project,
Portfolio,
Program
FROM
DISTINCT.xlsx
(ooxml, embedded labels, table is Sheet1)
Where ProjectCode <> Previous(ProjectCode);
what row you want to delete
?
This one is slightly better
DISTINCT:
LOAD distinct ProjectCode,
Project,
Portfolio,
Program
FROM
DISTINCT.xlsx
(ooxml, embedded labels, table is Sheet1);
Final_DISTINCT:
NoConcatenate
LOAD *
Resident DISTINCT
Where ProjectCode <> Previous(ProjectCode)
Order By ProjectCode;
DROP Table DISTINCT;
May be try like this not tested
load distinct * inline [
your data
];
If you don't care about which of the other values you get, maybe can you do something like:
DISTINCT:
LOAD distinct ProjectCode,
FirstSortedValue(Project),
FirstSortedValue(Portfolio),
FirstSortedValue(Program)
FROM
DISTINCT.xlsx
(ooxml, embedded labels, table is Sheet1)
GROUP BY ProjectCode
ORDER BY ProjectCode
From doesn't allow for Order By Statement, beside you don't even need Order By here because you are using Group by and FirstSortedValue.
But having said that, I would go with Where ProjectCode <> Previous(ProjectCode) because Group by will give slower performance on a bigger dataset compared to Previous. What might help is if the data is already sorted by ProjectCode and then we won't even need a resident load and my first solution will work.
Hi Xia,
I have attached one qvw file. Please have look into it.
Hope this is what you are looking for.
If this answer helps, then mark it correct so that we can close this call.
Thanks
Regards,
Alkis Michael
Hi I am not able to open a qvf file since I only have Qliview.
Hi Xia,
Please find attached qvw file.
Regards,
Alkis Michael !