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: 
james_hanify
Creator
Creator

creating a flag for collaborative

Hi,

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.

James.

9 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Could you provide the improvised data and the output table?

vikasmahajan

Not sure but you can try this

= Sum(Aggr(If(Count(invoice)>1,1,0),customer,consultant))      heading FLAG

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
james_hanify
Creator
Creator
Author

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

I24,11,G000AAA,1234556

effinty2112
Master
Master

Hi James,

                    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.

cheers

Andrew

jonathandienst
Partner - Champion III
Partner - Champion III

As a front end expression:

=Sum(Aggr(If(Count(DISTINCT consultant) > 1, [Invoice number]), 1, 0))

Or in the back end:

Join(Data)

LOAD [Invoice number]

  If(Sum(DISTINCT consultant) > 1, 1, 0) as Flag

Resident Data

Group By [Invoice number];

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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];

Result:

Screenshot_1.jpg

Take a look to the attachment.

effinty2112
Master
Master

Hi James,

               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

TempCollaboration:

Load

[Invoice number],

if(CountConsultants >1,'Yes','No') as Collaboration

;

Load

[Invoice number],

Count(DISTINCT Consultant) as CountConsultants

Resident TableName Group by [Invoice number];

MappingCollaboration:

Mapping

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.

Finally

Left Join(TableName)

LOAD

DISTINCT

[Invoice number],

ApplyMap('MappingCollaboration',[Invoice number]) as Collaboration

Resident TableName;

Again apologies for any errors as the script has not been tested

Cheers

Andrew

james_hanify
Creator
Creator
Author

Hi,

I like the idea of the front end, but it didn't seem to work for me

james_hanify
Creator
Creator
Author

Hi all,

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.

i.e.

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.