Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In the Load Editor, i have a table called [MAIN] which contains the full scope of ID.... and then i have another table called [IMPORTANT] which contains a list of Important ID and a tag field showing 'Yes'. The dashboard is associated based on ID, so as you can imagine when i have a full list of ID and then the column Tag next to it, it will show Yes for the Important however will show a dash for when there is no value.
I need to get a field in [MAIN] that essentially shows yes for when important and also no for when not important.
Issue i have is the [MAIN] table is loaded in before the [IMPORTANT] table.
Please help
Hello, are you doing a join? Can you show what your script looks like?
the info is confidential but i can give an example.
[MAIN]:
Load
[ID],
*,
From FILE
// then on a different tab in the load editor
[IMPORTANT]:
Load
[ID],
'Yes' as [Tag],
From FILE
so lets assume main table has 100 ID and the Important has 20 ID.. for the remaining 80 i want a field in MAIN that Yes/No.
You can try something like this
[MAINtemp]:
Load
[ID],
*,
From FILE
left join
[IMPORTANT]:
Load
[ID],
'Yes' as [Tag],
From FILE
NoConcatenate
[MAIN]:
Load
[ID],
if(TAG='YES','YES','NO')
*
From FILE
drop table MAINtemp;
With this the TAG is found in the Main table but there is the possibility of keeping an Important table
@Clement15 solution would work perfectly but if due to some reason you cant create a temp table,
try this :-
[MAIN]:
Load
[ID],
*,
From FILE
// then on a different tab in the load editor
[IMPORTANT]:
Load
[ID],
'Yes' as [Tag],
From FILE
T:
Mapping load [ID],'YES' as [Tag] resident [Important];
left join([MAIN])
Load [ID],applymap('T',[ID],'NO') as TAG resident [MAIN];
PS: If your [IMPORTANT] Table is not important except for [TAG] field, you can convert it to Mapping Table.
Now if you dont want to change the script and just want to do it in front end create a master dimension named [TAG] and set its expression to :- aggr(Coalesce([TAG],'NO'),[ID])