topic Re: Flag in load script in QlikView

Flag in load script

Anonymous — Mon, 19 Jun 2017 08:24:18 GMT

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

Re: Flag in load script

sunny_talwar — Mon, 19 Jun 2017 10:23:54 GMT

Would you be able to share few rows of data and the expected output from the data you have shared?

Re: Flag in load script

Anonymous — Mon, 19 Jun 2017 10:50:37 GMT

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!

Re: Flag in load script

sunny_talwar — Mon, 19 Jun 2017 11:01:47 GMT

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;

Re: Flag in load script

Anonymous — Mon, 19 Jun 2017 11:03:57 GMT

Oh I meant 'Object.Number'. Does that make any difference?

Re: Flag in load script

sunny_talwar — Mon, 19 Jun 2017 11:06:00 GMT

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;

Re: Flag in load script

Anonymous — Mon, 19 Jun 2017 11:16:46 GMT

Thank you very much. That works!

Could you explain to me HOW it works?

Best regards

Lukas

Re: Flag in load script

sunny_talwar — Mon, 19 Jun 2017 11:20:27 GMT

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?

Re: Flag in load script

Anonymous — Mon, 19 Jun 2017 11:23:39 GMT

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!

Re: Flag in load script

melissapluke — Fri, 06 Sep 2019 20:30:07 GMT

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