Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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( ...
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