Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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
Partner

Could you provide the improvised data and the output table?

vikasmahajan
Champion
Champion

Not sure but you can try this

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

Vikas

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

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
Partner

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.