Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I wish Qlik would give more information about where the actual error is, as I really can't see it!!!
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;
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.
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;
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?
Could you share the complete script?
Sure, the original data load is:
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:
Thanks
that's not worked unfortunately........ will have to go back to the drawing board.
I think I would do it in this way:
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;