Table script help

[davehutchinson]

Hi there.

I'm going round in circles trying to create a table in the load editor, and can't for the life of me work out where I'm going wrong.!

In my original data set I have the following columns:

SiteName

Agent_ID

I want to create a new table "Site_Agents_Table" that will count the number of agents in each site, and then categorise those sites with either "less than 10" or "More than 10" agents.

My script is:

Site_Agents_Table:

LOAD 

    [SiteName] as SiteAgents_Site,

    Agent_ID as SiteAgents_Agent,

    Count(DISTINCT[Agent_ID]) as Agents_Count,

    IF(Count(DISTINCT[Agent_ID])>9,'10 or More Agents', IF(Count(DISTINCT[Agent_ID])<10,'Less than 10')) as SiteAgents_Category

    Resident [SA_Usage] Group By [SiteName];

Looks ok to me, but when i load the data I get this error:

I wish Qlik would give more information about where the actual error is, as I really can't see it!!!

    Can anyone help?

Thanks

[dmac1971]

Hi you need to load the data first in a tmp table for example, then reload and group.  Once you have done this you can check qtys etc.  Read up on group.  See how you get on.

[BrunPierre]

The error occurs because any field that is being loaded but not aggregated must be explicitly included in the "GROUP BY" clause.

Here's how I would approach it.

Site_Agents_Table:
LOAD *,
If(Agents_Count > 9, '10 or More Agents',
If(Agents_Count < 10, 'Less than 10')) as SiteAgents_Category;

LOAD
[SiteName] as SiteAgents_Site,
Agent_ID as SiteAgents_Agent,
Count(DISTINCT[Agent_ID]) as Agents_Count
Resident [SA_Usage]
Group By [SiteName], Agent_ID;

[davehutchinson]

So I've got all the fields loaded in the previous table:

And then in my new table, I've brought in those fields and renamed them so they don't get double counted with the previous table.  Then I've got my count and category fields, using the 2 fields I've brought into the new table.

After that i've got my resident table, naming the previous dataset, and groupby clause naming the original field.

When I load, it says "The following error occurred: "Field 'SiteAgents_Agent' not found.  but I'm not sure why it can't find it?

[BrunPierre]

Could you share the complete script?

[davehutchinson]

Sure, the original data load is:

SA_Usage:

LOAD

    WeekCommencing,

   // date([WeekCommencing], 'DD/MM/YYYY') as WeekCommencing,

WEEK([WeekCommencing]) as WeekNo,

    Right([WeekCommencing],4) as Year,

    WeekName([WeekCommencing]) as YrWk,

    Agent_ID,

    Agent_Profile,

    HomeViewSessions,

    SmartAssistantIssueDetected,

    SmartAssistantUsed,

    IssueDetectedPercent,

    SmartAssistantUsagePercent,

    TotalHomviewTimeSeconds,

    AverageHomeViewSessionTime,

    EmployeeEIN,

    EmployeeManager,

    SiteName,

    IF(WildMatch([SiteName],'Concentrix - Dublin', 'Concentrix - Quorum', 'Concentrix - Stockport', 'Darlington', 'Exeter', 'Infosys - Clonmel', 'Infosys - Waterford', 'Liverpool', 'Newcastle', 'Snowhill', 'Warrington', 'WebHelp - Falkirk'),

    'Y','N') as "KeySite-Y/N"

FROM [lib://AttachedFiles/HomeViewSmartAssistantUsagebyUser_100624.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

and then in a different tab, I've created a new table, which is where the issue is.  But I'm not sure where I'm going wrong:

Site_Agents_Table:

LOAD 

SiteName as SiteAgents_Site,

    Agent_ID as SiteAgents_Agent,

    Count(DISTINCT[SiteAgents_Agent]) as Agents_Count,

    IF(Count(DISTINCT[SiteAgents_Agent])>9,'10 or More Agents', IF(Count(DISTINCT[SiteAgents_Agent])<10,'Less than 10')) as SiteAgents_Category

    Resident [SA_Usage]

    Group By [SiteName];

Thanks

[davehutchinson]

that's not worked unfortunately........ will have to go back to the drawing board.

[marcus_sommer]

I think I would do it in this way:

left join([SA_Usage])

LOAD 

    [SiteName],

    IF(Count(DISTINCT [Agent_ID])>9,'10 or More Agents', 'Less than 10')) as SiteAgents_Category

Resident [SA_Usage] Group By [SiteName];

[davehutchinson · Accepted solution]

Thanks all.

I've managed to make it work by splitting the table into 2, and then adding a "drop table" command.

Site_Agents_Count:
LOAD
SiteName as SiteAgents_Site,
Count(Agent_ID) as Agents_Count

Resident [SA_Usage] Group By SiteName;

Site_Agents_Table:
LOAD
SiteAgents_Site,
Agents_Count,
IF(Agents_Count > 9, '10 or More Agents', 'Less than 10') as SiteAgents_Category
Resident Site_Agents_Count;

DROP TABLE Site_Agents_Count;