Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

patriciousa
Contributor

If multiple values/possibilities

Hi Community.

I would really appreciate some help with this. I couldn't think of anything better for the topic title..

This is my data:

   

Acreedor  IDCODE
CL91037   272693254750070006130004069851                                     
CL91038   202247126000070006130004069851                                     
LO10061   307098777780070010820000199503                                     
LO100612  307088041810070010820000199503                                    
CL91013   202095632670070010830004006181                                    
LO08530   202095632670070010830004006181                                     
CL90620         0070018430004029351                                     
LO09263   273164876400070018430004029351                                     
LO09857   337122714990070019120000008792                                     
LO10931   307120979450070019120000008792                                     
CL91087   202229375240070019130004021429                                     
LO10087   202229375240070019130004021429                                     
LO10978   202838048100070020730004032600                                     
LO109786  232589495790070020730004032600                                     

What I need:

    

OutputAcreedor  IDCODE
SAME CODE, DIFFERENT IDCL91037   272693254750070006130004069851                                     
SAME CODE, DIFFERENT IDCL91038   202247126000070006130004069851                                     
SAME CODE, DIFFERENT IDLO10061   307098777780070010820000199503                                     
SAME CODE, DIFFERENT IDLO100612  307088041810070010820000199503                                    
SAME CODE, SAME IDCL91013   202095632670070010830004006181                                    
SAME CODE, SAME IDLO08530   202095632670070010830004006181                                     
SAME CODE, SAME IDCL90620         0070018430004029351                                     
SAME CODE, SAME IDLO09263   273164876400070018430004029351                                     
SAME CODE, DIFFERENT IDLO09857   337122714990070019120000008792                                     
SAME CODE, DIFFERENT IDLO10931   307120979450070019120000008792                                     
SAME CODE, SAME IDCL91087   202229375240070019130004021429                                     
SAME CODE, SAME IDLO10087   202229375240070019130004021429                                     
SAME CODE, DIFFERENT IDLO10978   202838048100070020730004032600                                     
SAME CODE, DIFFERENT IDLO109786  232589495790070020730004032600                                     

In case the output table is not self explanatory, I need to create a dimension with 3 possible outcomes:

1. The "Acreedor" has one single ID for one single CODE (No examples in this table)

2. The "Acreedor" has one single ID, but 2 or more CODES

3. The "Acreedor" has 2 or more IDs, but one single CODE.

Thank you in advance.
Kind regards.

3 Replies
sumit_ranjan_pa
Contributor

Re: If multiple values/possibilities

How are you getting the "SAME CODE, DIFFERENT ID" values ? What is the logic behind it? If you can explain that may be we can help you out.

rawoeste
New Contributor II

Re: If multiple values/possibilities

Create a temp table?

- Acreedor, count distinct ID, count distinct CODE

Join on your base table (just on the Acreedor field).

You will have something like:

AcreedorIDCODE

NewCountID

NewCountCode
CL91037 272693254750070006130004069851                                    12
CL91038 202247126000070006130004069851                                    12
LO10061 307098777780070010820000199503                                    12
LO100612307088041810070010820000199503         12

From there, add new column with your logic?

if(NewCountID > 1 and NewcountCode = 1, 'same code different ID',

     if( ...

Re: If multiple values/possibilities

Not entirely sure about your request, But this is what i understand from explanation. Perhaps this?

If(ID = Previous(ID), Peek('Output') & AutoNumberHash256(ID)) as Output

Life is so rich, and we need to respect to the life !!!
Community Browser