topic Re: How do I only load distinct? in QlikView

How do I only load distinct?

Wed, 07 Dec 2016 12:13:43 GMT

Here is the code that I am using to populate my dashboard with data.

Table1:

Load

ITMSNumber,

Prioritization,

if(isnull(SCA),'No','Yes') as SCA,

if(ITMSNumber like 'X*',1,0) as SCA_Extract,

if(DATALOADSTATUSID=25, 'Available - SCA-V', if(DATALOADSTATUSID=26, 'Available - DZ', if(DATALOADSTATUSID=30, 'Quarantined - Coming Soon', if(DATALOADSTATUSID=40, 'Available - DSC', if(DATALOADSTATUSID=50, 'Available - DSC, Partial Load'))))) as DATALOADSTATUS1,

DATALOADSTATUSID,

IT_DSCRequest_RegisterNumber as [DSC CCR Number],

LOADPRIORITYSTATUSID;

SQL SELECT ITMSNumber,IT_DSCRequest_RegisterNumber,Prioritization,SCA,DATALOADSTATUSID,ITMS_App_Status_ID,

LOADPRIORITYSTATUSID

FROM MPL.dbo.tblSTRATEGY

where LOADPRIORITYSTATUSID=10 or LOADPRIORITYSTATUSID=20 or LOADPRIORITYSTATUSID=30 or LOADPRIORITYSTATUSID=40 or LOADPRIORITYSTATUSID=50 and (LEN(DATALOADSTATUSID) >0);

RENAME FIELD Prioritization TO [Pr Yr];

Left Keep

Table2:

Load

DOINVSTATUSID,

if("DOMAIN_SKILLTEAM"='QUAL','Quality',

if("DOMAIN_SKILLTEAM"='PRD', 'Product Development',

if("DOMAIN_SKILLTEAM"='TP', 'Third Party',

if("DOMAIN_SKILLTEAM"='MSS', 'Marketing & Sales',

if("DOMAIN_SKILLTEAM"='HR', 'Human Resources',

if("DOMAIN_SKILLTEAM"='PUR', 'Purchasing',

if("DOMAIN_SKILLTEAM"='IT', 'Information Technology',

if("DOMAIN_SKILLTEAM"='FIN', 'Finance',

if("DOMAIN_SKILLTEAM"='CRS', 'Credit',

if("DOMAIN_SKILLTEAM"='MFG', 'Manufacturing',

"DOMAIN_SKILLTEAM")))))))))) as SkillTeam,

GDIAITSTATUSID,

ITMSNumber,

Description,

LOWER(DATASTRATEGYAUTHORCDSID) as 'Data Steward',

LOWER(DATASTRATEGYL6CDSID) as 'Data Steward Supervisor',

GDIAIT_Notes as 'IT notes',

Notes as 'Data Ops Notes';

SQL SELECT DOINVSTATUSID,

"DOMAIN_SKILLTEAM",

GDIAITSTATUSID,

ITMSNumber,

Description,

DATASTRATEGYAUTHORCDSID,

DATASTRATEGYL6CDSID,

Notes,

GDIAIT_Notes

FROM MPL.dbo.tblAPPINVENTORY

WHERE (LEN(DOINVSTATUSID) >0);

Left Keep

SQL SELECT Acronym,

ITMSNumber,

Name

FROM MPL.dbo.tblBASIC;

Left Keep

SQL SELECT DOINVSTATUS,

DOINVSTATUSID,

DOINVSTATUSSORTORDER

FROM MPL.dbo."tblSTATUS-DATAOPSINVENTORY"

WHERE (LEN(DOINVSTATUSID) >0);

Left Keep

Table3:

Load

ITMS_Number as ITMSNumber,

IncompleteFields,

PercentComplete,

num(PercentComplete, '#.%') as '% Complete';

SQL SELECT PercentComplete,

IncompleteFields,

ITMS_Number

FROM MPL.dbo."tblQV_DASHBOARD_METRICS";

SQL SELECT GDIAITSTATUS,

GDIAITSTATUSID

FROM MPL.dbo."tblSTATUS-GDIAIT";

SQL SELECT DATALOADSTATUS,

DATALOADSTATUSID,

DATALOADSTATUSSORTORDER

FROM MPL.dbo."tblSTATUS-DATALOAD"

WHERE (LEN(DATALOADSTATUSID) >0);

Left Keep

Table4:

Load

ITMSNumber,

SupportApplicationSupervisor as 'AM Contact';

SQL SELECT SupportApplicationSupervisor,

ITMSNumber

FROM MPL.dbo."tblContact";

SQL SELECT dbo.tblITMS_Profile.[ITMS Type], Count(dbo.tblITMS_Profile.[ITMS Type]) AS [Count of ITMS Type], dbo.tblITMS_Profile.[Application Status] AS [Status]

FROM dbo.tblITMS_Profile

WHERE dbo.tblITMS_Profile.[Application Status] = 'In Production'

GROUP BY dbo.tblITMS_Profile.[ITMS Type], dbo.tblITMS_Profile.[Application Status]

ORDER BY dbo.tblITMS_Profile.[ITMS Type];

SQL SELECT Count(*) AS Total

FROM dbo.tblITMS_Profile

I'm trying to only load distinct values by ITMSNumber but there's one text object that when I click it, it adjusts a straight table and has 2 of the same ITMSNumber's. I have no idea why it's doing this. This is the final touch that my dashboard needs for piloting and I can't seem to get it straight.

Re: How do I only load distinct?

Gysbert_Wassenaar — Wed, 07 Dec 2016 12:19:06 GMT

You use the DISTINCT keyword to specify that a load should be distinct. That works in SELECT and LOAD blocks.

Table1:

LOAD DISTINCT a, b, c, ... FROM .... ;

Table2:

SELECT DISTINCT p, q, r, ... FROM .... ;

Re: How do I only load distinct?

Wed, 07 Dec 2016 12:21:13 GMT

That is not working though... is there a work around? Can I do this without altering the code?

Re: How do I only load distinct?

Gysbert_Wassenaar — Wed, 07 Dec 2016 12:23:23 GMT

What do you mean "not working". There is no way a LOAD DISTINCT will load duplicate records.

Re: How do I only load distinct?

Wed, 07 Dec 2016 12:25:14 GMT

That's exactly why this is perplexing.

The problem is that the only part of the row that is a duplicate is the ITMSNumber. I need to get rid of all of the others that have the same ITMSNumber.

Re: How do I only load distinct?

Gysbert_Wassenaar — Wed, 07 Dec 2016 12:27:28 GMT

Where do the other parts come from? Those parts are making the rows unique.

Re: How do I only load distinct?

Wed, 07 Dec 2016 12:28:07 GMT

They come from a database. Does that answer your question?

Re: How do I only load distinct?

Gysbert_Wassenaar — Wed, 07 Dec 2016 12:36:37 GMT

It answers the question. It doesn't help however. I cannot mind read, nor access your data remotely so unless you give information there's nothing I can do for you. Post a small Qlikview document that demonstrates the problem.

Re: How do I only load distinct?

Wed, 07 Dec 2016 12:45:35 GMT

I don't know how to do that...

Re: How do I only load distinct?

Gysbert_Wassenaar — Wed, 07 Dec 2016 12:50:43 GMT

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: How do I only load distinct?

wizardo — Tue, 12 Sep 2017 10:54:53 GMT

Hi,

The DISTINCT keyword in load statement is meant to remove duplicate Records (that is. Records that ALL fields have the same values will load only once)

if you want to remove Records that one of the fields is the same value as in another raw, you must ask yourself which of the Records you want to remove. if it doesn't matter then you should use this syntax:

load

* \\ list all the fields in your load statment

from [YourTable]

where not exists ( ITMSNumber , ITMSNumber);

// the second parameter is redundent in this case since its the default, but i included it for the form of the function to be clearer

the function syntax is Exists(FieldName, Expression)

what it does is check if the value in the "Expression" is one of the values loaded so far into "FieldName"

in our case it reads a raw from the source table and checks if the value of the field "ITMSNumber" in that raw is already loaded into this field, if NO, (like in the first record) it loads that raw into memory

if YES, then the raw is skipped and qlik reads the next raw and repeats the process

in your case it means it will only one raw for each value in the "ITMSNumber" field.

btw it will keep only the raw where each value first appears.

based on the load order.

hope that helps/clerifes

Mansyno