Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello good people here at QlikCommunity.
This forum is great and as a newbie to Qlikview I learn a lot. I could do with help on the following task though:
I have a table with Orders by Date, Status and Company. I now need to achieve the following:
Create a "Company Size" field:
No order in the last 30 days = "No Orders"
Less than 50 orders in the last 30 days = "Small Size Company"
Between 50 and 100 order in the last 30 days = "Medium Size Company"
More than 100 orders in the last 30 days = "Large Size Company"
Only Orders with a "Closed" status should be considered
I will need this new field "Company Size" for other charts, so perhaps it should go into the loading script?
I attach a sample sheet.
Thanks for all your contributions on this forum!
Hi,
I would try something like the below,
SizeData:
LOAD OrderID,
Date,
Status,
Company
FROM
[..\qlikdata.xlsx]
(ooxml, embedded labels);
NoConcatenate
Temp:
LOAD OrderID,
Date,
Company
Resident SizeData
Where Date >= Today()-30;
CompanySize:
LOAD
Company,
COUNT(OrderID) as OrderCount,
IF(COUNT(OrderID) = 0,
'Small Size Company',
IF(COUNT(OrderID) < 50,
'Medium Size Company',
'Large Size Company'
)
) as CompanySize
Resident Temp
Group By Company;
Drop Table Temp;
Mark
Hi,
I would try something like the below,
SizeData:
LOAD OrderID,
Date,
Status,
Company
FROM
[..\qlikdata.xlsx]
(ooxml, embedded labels);
NoConcatenate
Temp:
LOAD OrderID,
Date,
Company
Resident SizeData
Where Date >= Today()-30;
CompanySize:
LOAD
Company,
COUNT(OrderID) as OrderCount,
IF(COUNT(OrderID) = 0,
'Small Size Company',
IF(COUNT(OrderID) < 50,
'Medium Size Company',
'Large Size Company'
)
) as CompanySize
Resident Temp
Group By Company;
Drop Table Temp;
Mark
Hi Mark,
thanks so much for your help, this is great!
I amended the IF statement slightly to get what I want:
CompanySize:
LOAD
CompanyID,
COUNT(CaseID) as OrderCount,
if(COUNT(CaseID) = 0,'No Orders',
if(COUNT(CaseID) >0 AND COUNT(CaseID) <= 5,'Small',
if(COUNT(CaseID) >5 AND COUNT(CaseID) <= 20,'Medium','Large')))
as CompanySize
Works like a charm and just what I needed.