Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I've got a problem with setting a flag in Qlikview load script.
What I want to do is set a Flag to all Order Numbers where Sales(Income) is recorded.
I've tried the following statement:
NoConcatenate
Fact_new:
load *,
if(rangesum(if(Cost.Element.MiKa='Sales',Actual))<>0 or rangesum(if(Cost.Element.MiKa='Income',Actual))<>0,'billed','not billed') as Billing.Indicator.Sales
Resident
Fact;
Now I got a Flag to the Order Number with a recorded Sales and where Cost Element=Sales(Income). What I want to achieve is to set a flag to all order numbers where sales(income) <>0 regardless the cost element to show the CMIIa.
Is this possible?
Thanks in advance
Lukas
Then try this
Table:
LOAD Sales.Number,
Object.Number,
Document.Number,
%Date,
Cost.Element.MiKa,
Actual
FROM ....;
Left Join (Table)
LOAD Sales.Number,
Object.Number,
If(Count(DISTINCT If(Cost.Element.MiKa = 'Sales', Object.Number) = 1, 'billed', 'not billed') as Billing.Indicator.Sales
Resident Table
Group By Sales.Number, Object.Number;
Would you be able to share few rows of data and the expected output from the data you have shared?
Sure!
That's a pretty good example. As you can see within the Order Number Sales is recorded. The Flag 'Billing.Indicator.Sales' shows billed but only where Cost Element is 'Sales'.
I want to have 'billed' on every entry of this Order Number because sales is recorded.
Thanks in advance Sunny!
When you say Order Number, do you mean Sales.Number field? May be you need to do like this
Table:
LOAD Sales.Number,
Object.Number,
Document.Number,
%Date,
Cost.Element.MiKa,
Actual
FROM ....;
Left Join (Table)
LOAD Sales.Number,
If(Count(DISTINCT If(Cost.Element.MiKa = 'Sales', Sales.Number) = 1, 'billed', 'not billed') as Billing.Indicator.Sales
Resident Table
Group By Sales.Number;
Oh I meant 'Object.Number'. Does that make any difference?
Then try this
Table:
LOAD Sales.Number,
Object.Number,
Document.Number,
%Date,
Cost.Element.MiKa,
Actual
FROM ....;
Left Join (Table)
LOAD Sales.Number,
Object.Number,
If(Count(DISTINCT If(Cost.Element.MiKa = 'Sales', Object.Number) = 1, 'billed', 'not billed') as Billing.Indicator.Sales
Resident Table
Group By Sales.Number, Object.Number;
Thank you very much. That works!
Could you explain to me HOW it works?
Best regards
Lukas
You were looking at each and every row to check for your if statement requirement. I checked at an aggregate level that Count(DISTINCT Object.Number) where any of the Cost.Element.MiKa = Sales. If It is, then call all of the Object.Number as billed, other wise call it not billed.
Does this make sense?
That was exactly what I was looking for but had no idea how to write it in the load script!
Many thanks Sunny!
Guess this will help me in some other cases, too!
Hi Sunny,
You helped me with a similar issue a while ago and we came up with a different solution. I've tried this way you suggested above, along with the original way you suggested to me and both yield the same correct results. Do you have any idea which is more efficient?
If it matters I have 10 different flags to set based on different values in the field that equate to this example's Cost.Element.MiKa.
Here is the other solution:
Flags:
Load distinct
Sales.Number,
Sales.Number as Temp.Sales.Number,
dual('Yes',1) as Billing.Indicator.Sales
Resident Table
where Cost.Element.Mika='Sales';
Load distinct
Sales.Number,
Sales.Number as Temp.Sales.Number,
dual('No',0) as Billing.Indicator.Sales
Resident Table
where not Cost.Element.Mika='Sales' and not exists (Temp.Sales.Number, Sales.Number);
Any help would be appreciated.
Thanks!
Melissa