topic Re: Load Distinct in QlikView

Load Distinct

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

Hi community,

I want Load 4 fields from a resident table. In this table i have ID_Employee, Name Employee, Function, UnityBussiness.

I want load this where ID_Employee must be distinct. Any ideas?

Thanks in advance
Best Regards

Bruno Paulo

Re: Load Distinct

vishsaggi — Tue, 28 Nov 2017 14:29:47 GMT

Did you try like

Load DISTINCT ID_Employee, NameEmployee, Function, UityBusiness

FROM yourtablename;

Re: Load Distinct

brunopaulo — Tue, 28 Nov 2017 14:31:55 GMT

yes, didnt work

Re: Load Distinct

el_aprendiz111 — Tue, 28 Nov 2017 14:33:15 GMT

Hi Bruno

Would you be able to share a sample with the expected output?

Re: Load Distinct

vishsaggi — Tue, 28 Nov 2017 14:33:28 GMT

can you share some sample data to look into?

Re: Load Distinct

Mark_Little — Tue, 28 Nov 2017 14:36:51 GMT

Hi,

You would expect the distinct to work if they are true duplications.

Is it the case that the other fields (Name Employee, Function, UnityBussiness) have different values in?

If that is the case then which instance would like to keep the first instance?

Mark

Re: Load Distinct

antoniotiman — Tue, 28 Nov 2017 14:38:31 GMT

Try

LOAD

ID_Employee, FirstValue(NameEmployee) as NameEmployee,FirstValue( Function) as Function, FirstValue(UnityBusiness) as UnityBusiness

Resident Table

Group By ID_Employee;

Drop Table Table;

Re: Load Distinct

brunopaulo — Tue, 28 Nov 2017 15:01:33 GMT

its hard to me to create sample but i can try explain

ID Name Function Unity

1 Bruno cooker kitchen

2 Andrew engineer company

2 andrew engineer company

I want to distinct load with base on id. Because some names, function and unity can repeat

I want

ID Name Function Unity

1 Bruno cooker kitchen

2 andrew engineer company

Re: Load Distinct

Mark_Little — Tue, 28 Nov 2017 15:16:24 GMT

QlikView is case sensitive, so for your example Andrew and andrew are different, so for you load would be ignored by the Distince in the load. Maybe try cleaning the the data.

Something like

LOAD DISTINCT

Capitalize(TRIM(ID_Employee)) as ID_Employee,

Capitalize(TRIM(NameEmployee)) as NameEmployee,

Capitalize(TRIM(Function)) as Function,

Capitalize(TRIM(UityBusiness)) as UityBusiness

From....

Re: Load Distinct

trdandamudi — Tue, 28 Nov 2017 15:28:04 GMT

One way is as below:

Source_Temp:
Load * Inline [
ID, Name, Function,Unity
1, Bruno,cooker,kitchen
1, Bruno,cooker,kitchen
2, Andrew,engineer,company
2, andrew,engineer,company
];

NoConcatenate
Final_Table:
Load *
Resident Source_Temp
Where Peek(ID) <> ID;

Drop Table Source_Temp;

Re: Load Distinct

vishsaggi — Tue, 28 Nov 2017 15:32:38 GMT

May be another way ?

Tab:

LOAD *, IF(ID = Previous(ID), 0, 1) AS RowNum INLINE [

ID, Name, Function, Unity

1, Bruno, cooker, kitchen

2, Andrew, engineer, company

2, andrew, engineer, company

];

NoConcatenate

Final:

LOAD *

Resident Tab

Where RowNum = 1;

Drop Table Tab;

Re: Load Distinct

Aurelien_Martinez — Tue, 28 Nov 2017 15:53:02 GMT

Hi,

Source_Temp:

Load * Inline [

ID, Name, Function,Unity

1, Bruno,cooker,kitchen

2, Andrew,engineer,company

2, andrew,engineer,company

];

NoConcatenate

LOAD

ID as ID2

Resident Source_Temp

Where not Exists(ID2, ID)

;

DROP Table Source_Temp;

Re: Load Distinct

brunopaulo — Thu, 30 Nov 2017 10:21:10 GMT

Sorry, is hard for me create examples, but i solved the problem. Thank you

Re: Load Distinct

brunopaulo — Thu, 30 Nov 2017 10:21:58 GMT

Yes dat was my error Andrew should be equal. But i solved the problem Thank you

Re: Load Distinct

balar025 — Thu, 30 Nov 2017 10:55:25 GMT

I think this should work

Source_Temp:

Load *

where not exists(ID);

Load * Inline [

ID, Name, Function,Unity

1, Bruno,cooker,kitchen

2, Andrew,engineer,company

2, andrew,engineer,company

];

Re: Load Distinct

davidhechler — Mon, 13 Nov 2023 13:18:06 GMT

I know this has been resolved already, but couldn't you just use "Load Distinct"? It would be simpler, in my opinion.

E. g.:

Source_Temp: Load Distinct * Inline [ ID, Name, Function,Unity 1, Bruno,cooker,kitchen 1, Bruno,cooker,kitchen 2, Andrew,engineer,company 2, Andrew,engineer,company ]; Exit Script;

This also results in the follwing table: