Additional column adds row with if-condition

[Sandra5]

Hi,

I hope someone can help me with this problem. I have two lists with data. One contains dates, ID numbers of clients and several other columns. The second list also contains dates and the ID numbers, plus one column I need to add to the first list.

The ID numbers can appear several times in the data sources. So I created a unique identifier while loading the app - date+ID+3rd column. I created those identifiers while loading both lists into the app.

Then I created a simple table showing the different columns from the first list. The last column should now be the additional info I need from the second data source. I tried to use this condition:

=if(Identifier_1=Identifier_2,'Option_1','Option_2')

That works well, wenn the identifiers aren't the same in a given month, but if Option_1 is valid, it adds another row to my data with Option_2 as well.

E.g.

List1:

July, ID_001, Account1,X,Y,Z

July, ID_001,Account2,X,Y,Z

July, ID_002,Account3,X,Y,Z

List2:

July, ID_001, Account1,Yes

July, ID_001, Account2,Yes

Required Result:

July, ID_001, Account1,X,Y,Z,Option_1

July, ID_001,Account2,X,Y,Z,Option_1

July, ID_002,Account3,X,Y,Z,Option_2

Shown result with the condition:

July, ID_001, Account1,X,Y,Z,Option_1

July, ID_001, Account1,X,Y,Z,Option_2

July, ID_001,Account2,X,Y,Z,Option_1

July, ID_001,Account2,X,Y,Z,Option_2

July, ID_002,Account3,X,Y,Z,Option_2

that means, every time Option_1 should be the solution, I get an additional Option_2 line. Maybe someone has any helpful ideas how to get around that. I would really appreciate it!

[marcus_sommer · Accepted solution]

Join approaches - directly in the script or by assigning the tables in the data-model - could cause unwanted effects if they haven't a defined relationship.

Simpler and without risks to adjust the number of rows would be to resolve the task with a mapping. Maybe with something like:

m: mapping load F1 & '|' & F2 & '|' & F3 as Lookup, 'Option1' as Return
from List2;

final: load *, applymap('m', F1 & '|' & F2 & '|' & F3, 'Option2') as Option
from List1;

[TheLazyDeveloper]

[Sandra5]

thank you, I'll try that!

[Amit_Prajapati]

I also tried with the shared data below is the required way we can get it.

// Load List2 with a flag
List2:
LOAD
Month,
ID,
Account,
'Option_1' AS OptionFlag
INLINE [
Month, ID, Account, Status
July, ID_001, Account1, Yes
July, ID_001, Account2, Yes
];

// Load List1 and LEFT JOIN with List2
List1:
LOAD
Month,
ID,
Account
INLINE [
Month, ID, Account
July, ID_001, Account1
July, ID_001, Account2
July, ID_002, Account3
];

// Join OptionFlag from List2
LEFT JOIN (List1)
LOAD
Month,
ID,
Account,
OptionFlag
RESIDENT List2;

drop table List2;

// Assign Option_2 if no match found
FinalTable:
LOAD
Month,
ID,
Account,
IF(LEN(TRIM(OptionFlag)) > 0, OptionFlag, 'Option_2') AS FinalOption
RESIDENT List1;

drop Table List1;

exit Script;

By using above script you can get your required.

Let me know if it's still not working.

[Sandra5]

Thanks for your input! This one doesn't work, because I have around 40k lines of data and they change every month. The mapping doesn't quite work yet but I'm confident it will.

[Sandra5]

I really like this option but I'm a bit stuck implementing it.

First, I loaded my two lists - or rather, my two types of lists. I get a new one every month. After, I added your mapping load like this, LL and Best are my two lists, and I replaced the F1-F3 with the field names of those tables:

m: mapping load LL_ID & '|' & LL_Account & '|' & Date as Lookup, 'Option1' as Return from LL;

final: load *, applymap('m', Best_ID & '|' & Best_Account & '|' & Date, 'Option2') as Option from Best;

When I try loading the data though, I get an error message for the mapping load: No qualified path for file: ***

I'm not sure where I went wrong.

[Amit_Prajapati]

If you are getting an error like no  qualified path me you need to check you from statment from where you are getting data it maybe excel or from others connection.

Make sure you have created a right connection to fetch or access the data 

[marcus_sommer]

It was a simplified example - just using from List# as source. But by using from you need to specify the source like: [Path\File.ext] (fileformat); respectively the data-base table. If the data are already loaded the reference as List# is valide but then it's called per resident instead of a from statement.

[Sandra5]

Thank you Marcus, that worked perfectly!