If multiple values/possibilities

[patriciousa]

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	ID	CODE
CL91037	27269325475	0070006130004069851
CL91038	20224712600	0070006130004069851
LO10061	30709877778	0070010820000199503
LO100612	30708804181	0070010820000199503
CL91013	20209563267	0070010830004006181
LO08530	20209563267	0070010830004006181
CL90620		0070018430004029351
LO09263	27316487640	0070018430004029351
LO09857	33712271499	0070019120000008792
LO10931	30712097945	0070019120000008792
CL91087	20222937524	0070019130004021429
LO10087	20222937524	0070019130004021429
LO10978	20283804810	0070020730004032600
LO109786	23258949579	0070020730004032600

What I need:

Output	Acreedor	ID	CODE
SAME CODE, DIFFERENT ID	CL91037	27269325475	0070006130004069851
SAME CODE, DIFFERENT ID	CL91038	20224712600	0070006130004069851
SAME CODE, DIFFERENT ID	LO10061	30709877778	0070010820000199503
SAME CODE, DIFFERENT ID	LO100612	30708804181	0070010820000199503
SAME CODE, SAME ID	CL91013	20209563267	0070010830004006181
SAME CODE, SAME ID	LO08530	20209563267	0070010830004006181
SAME CODE, SAME ID	CL90620		0070018430004029351
SAME CODE, SAME ID	LO09263	27316487640	0070018430004029351
SAME CODE, DIFFERENT ID	LO09857	33712271499	0070019120000008792
SAME CODE, DIFFERENT ID	LO10931	30712097945	0070019120000008792
SAME CODE, SAME ID	CL91087	20222937524	0070019130004021429
SAME CODE, SAME ID	LO10087	20222937524	0070019130004021429
SAME CODE, DIFFERENT ID	LO10978	20283804810	0070020730004032600
SAME CODE, DIFFERENT ID	LO109786	23258949579	0070020730004032600

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.

[sumit_ranjan_pa]

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]

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:

Acreedor	ID	CODE	NewCountID	NewCountCode
CL91037	27269325475	0070006130004069851	1	2
CL91038	20224712600	0070006130004069851	1	2
LO10061	30709877778	0070010820000199503	1	2
LO100612	30708804181	0070010820000199503	1	2

From there, add new column with your logic?

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

     if( ...

[Anil_Babu_Samineni]

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