How do I only load distinct?

[#1]

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.

[wizardo]

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