topic Re: Load Distinct problem in QlikView

Load Distinct problem

Anonymous — Fri, 16 Nov 2018 21:17:22 GMT

I have an app with multiple fact tables which i am trying to optimize. For that purpose trying to create a link table (have not included that part)

For doing this i am doing a load distnct

load

Distinct

Employee_ID,

Parent,

FactType

resident

MasterData

where FactType='xyz';

Master table has approx 120 million records.

the result of a expression count(distinct Employee_ID&Parent) is around 400k records

But somehow the load distinct doesnt work. the above load results in 120 million records

its weird. i remember reading somewhere about this but couldnt find the article again

but basically i replace above script with

load

Distinct

Employee_ID,

Parent,

MinString(FactType)

resident

MasterData

where FactType='xyz'

group by Employee_ID , Parent;

this one worked perfectly.

Can someone explain theory behind this?

@rwunderlich @Oleg_Troyansky

Re: Load Distinct problem

mikevwang — Sat, 17 Nov 2018 05:35:52 GMT

Are you doing a preceding load before this? If so, the distinct needs to be on the top load and any distinct in the following loads are ignored.

Re: Load Distinct problem

Anil_Babu_Samineni — Sat, 17 Nov 2018 06:17:07 GMT

Second script is not equal to first script. How you are comparing both scripts. DISTINCT Keyword removes only Unique rows. But, Qlik will automate that function.
MinString can help to show only one row as always based on aggregate to Emploee_ID and Parent.

Re: Load Distinct problem

Anonymous — Sat, 17 Nov 2018 06:36:57 GMT

No I am not doing a preceding load

Re: Load Distinct problem

Anonymous — Sat, 17 Nov 2018 06:38:18 GMT

I understand it's not technically equivalent. But Look at the where clause. The value on facttype column is just 'xyz'.
Still in the first load it doesn't give unique values. I tried distict on just Employee_ID and Parent with the where clause still doesn't work

Re: Load Distinct problem

Anil_Babu_Samineni — Sat, 17 Nov 2018 06:37:32 GMT

Can you try this?

Sum(Aggr(count(distinct Employee_ID&Parent), Empolyee_ID, Parent))

Re: Load Distinct problem

Gysbert_Wassenaar — Sat, 17 Nov 2018 06:54:09 GMT

Why do you think it doesn't do a distinct load? How are you checking that?
Try explicitly creating a new table so the data won't accidentally be appended to an existing table and store that table in a qvd so you can check outside of your qlikview document.

TempLink:
NOCONCATENATE
Load Distinct
    Employee_ID,
    Parent,
    FactType
resident
    MasterData
where 
    FactType='xyz';

STORE TempLink INTO [C:\CheckMe\CheckMe.qvd] (qvd);

Re: Load Distinct problem

Anonymous — Sat, 17 Nov 2018 11:15:14 GMT

Hi
Value for count(distinct Employee_ID&Parent)
with facttype filtered is around 400k.
The result of first load is the full 100 million rows.

Yes. I loaded the data in a separate qvw. Same result.
In fact found the work around with minstring in the separate qvw. I vaguely recall an article mentioning distinct doesn't always mean unique and to use aggregation to make it work. Not sure where I read it

Re: Load Distinct problem

Gysbert_Wassenaar — Sat, 17 Nov 2018 11:33:37 GMT

Ok, count(distinct Employee_ID&Parent) should always return the distinct number of combinations. But only if you really specify the distinct keyword in the expression. You're using that expression in a chart or text object, right?

Does it happen with a smaller data set too? Could you try to create a small qlikview document that reproduces the problem?

Re: Load Distinct problem

Anonymous — Sat, 17 Nov 2018 11:36:54 GMT

Yes. I am using it as a chart.

Will try and recreate on a smaller data set with hashed values. Not sure I can over the weekend the though

Re: Load Distinct problem

Anonymous — Sat, 17 Nov 2018 13:36:41 GMT

Got the answer some errant data in one of the fields were causing the issue. truncating the field solved the issue.
Knew it must have been something simple
thanks for the assistance