Discussion Board for collaboration on QlikView Scripting.
I am wondering whether anyone can provide me with some ideas on how to go about this.
Some of the fields I have to go by are
Invoice number, consultant, item and customer
What I would like to do is if invoice number exists more than once and consultant is different, a flag to designate that the consultant on this invoice was a collaborative effort.
Many thanks for your time.
Thanks, this is what I mean
Invoice number, consultant, item, customer
I23,10,G000AAA,1234556 should flag as collaborative
I23,11,G000AAA,1234556 should flag as collaborative
In a straight table with dimension [Invoice number] the expression:
if(Count(DISTINCT Consultant) >1,1,0) will return 1 for invoices that were collaborated on.
As a front end expression:
=Sum(Aggr(If(Count(DISTINCT consultant) > 1, [Invoice number]), 1, 0))
Or in the back end:
LOAD [Invoice number]
If(Sum(DISTINCT consultant) > 1, 1, 0) as Flag
Group By [Invoice number];
LOAD * INLINE [
Left join (tmp)
,count(Invoice) as [Invoice number]
group by Invoice;
if([Invoice number] > 1, 1, 0) as Flag_collaborate
DROP Table tmp;
DROP Field [Invoice number];
Take a look to the attachment.
If you want a flag in the script something like this could be used to create a mapping table, I haven't tested this so apologies for errors
if(CountConsultants >1,'Yes','No') as Collaboration
Count(DISTINCT Consultant) as CountConsultants
Resident TableName Group by [Invoice number];
LOAD * resident TempCollaboration;
DROP Table TempCollaboration;
Unfortunately if you try to make the top half of a preceding load as a mapping table it doesn't work so you need to make a temp table then make the mapping table from it then drop the temp table.
ApplyMap('MappingCollaboration',[Invoice number]) as Collaboration
Again apologies for any errors as the script has not been tested
I am struggling to apply any of this. I have made a new field which concatenates the key with the item allowing me to see if this appears twice for it to flag as a collaborative effort.
The data will be something like
XX*YYY*I234*ITEM and I can know from within my pivot table if that occurs twice it must be because the consultant is different, I know this may not be the best in terms of how the data looks, but even then I can't count it, I just don't understand why, is it because its not an integer? I tried Textcount and its the same thing.