Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Flag in load script

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

9 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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!

sunny_talwar

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;

Anonymous
Not applicable
Author

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

sunny_talwar

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;

Anonymous
Not applicable
Author

Thank you very much. That works!

Could you explain to me HOW it works?

Best regards

Lukas

sunny_talwar

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?

Anonymous
Not applicable
Author

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!  

melissapluke
Partner - Creator
Partner - Creator

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