Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I only load distinct?

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.

dupes.PNG

10 Replies
Gysbert_Wassenaar

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 .... ;


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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