topic Re: creating a flag for collaborative in QlikView

creating a flag for collaborative

james_hanify — Mon, 20 Jun 2016 09:15:33 GMT

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.

Re: creating a flag for collaborative

MindaugasBacius — Mon, 20 Jun 2016 09:39:06 GMT

Could you provide the improvised data and the output table?

Re: creating a flag for collaborative

vikasmahajan — Mon, 20 Jun 2016 09:40:01 GMT

Not sure but you can try this

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

Vikas

Re: creating a flag for collaborative

james_hanify — Mon, 20 Jun 2016 09:46:15 GMT

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

Re: creating a flag for collaborative

effinty2112 — Mon, 20 Jun 2016 09:57:17 GMT

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

Re: creating a flag for collaborative

jonathandienst — Mon, 20 Jun 2016 09:57:30 GMT

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

Re: creating a flag for collaborative

MindaugasBacius — Mon, 20 Jun 2016 09:58:56 GMT

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:

Take a look to the attachment.

Re: creating a flag for collaborative

effinty2112 — Mon, 20 Jun 2016 10:13:19 GMT

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

Re: creating a flag for collaborative

james_hanify — Mon, 20 Jun 2016 10:25:17 GMT

Hi,

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

Re: creating a flag for collaborative

james_hanify — Tue, 21 Jun 2016 16:21:18 GMT

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.