tmp: LOAD * INLINE [ Invoice, Consultant 1, A 1, B 2, C 3, D 2, A ]; Left join (tmp) LOAD Invoice ,count(Invoice) as [Invoice number] Resident tmp group by Invoice; Data: LOAD *, if([Invoice number] > 1, 1, 0) as Flag_collaborate Resident tmp ; DROP Table tmp; DROP Field [Invoice number];
Take a look to the attachment.
160620.qvw 146.0 K
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.