Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik community.
I need to create a table in my data load script with a column of 'Yes' or 'No' that indicates whether a data point exists in both of two other tables.
For example, in my dataset I have a table with a field called "EntityName" that includes all entity names. I have a second table with a field called "EntityName" that includes only entity names that have completed a process- we'll call those "AssignedEntityName".
What I need is a table with EntityName, AssignedEntityName, and "Assigned Y/N" that gives a result of "N" if AssignedEntityName is null. I have tried a few different things and nothing seems to work.
Edit: I was asked for sample data, so:
Table1 (Loaded from dataset):
Table2 (Loaded from dataset):
Desired Table (Created in script):
Please add some sample data and expected output for faster response
You can try something like this (not sure I have the table/field names correct, but the concept should work):
NewTableTemp:
Load EntityName
Resident Table1;
Left join (NewTableTemp)
Load AssignedEntityName as EntityName,
AssignedEntityName,
'Y' as [Assigned Y/N]
Resident Table2;
NoConcatenate
Load EntityName,
AssignedEntityName,
if(isnull([Assigned Y/N]),'N',[Assigned Y/N]) as [Assigned Y/N]
Resident NewTableTemp;
drop table NewTableTemp;
Unfortunately, this isn't working. I'm not getting 'N' for the nulls, just null. And I need to be able to count the Ns, so that is the critical part for me.
Okay. I made an adjustment. This works with test data that I think matches your general structure:
NoConcatenate
NewTableTemp:
Load EntityName,
EntityName as AssignedEntityName
Resident Table1;
Left join (NewTableTemp)
Load AssignedEntityName as EntityName,
AssignedEntityName,
'Y' as [Assigned Y/N]
Resident Table2;
NoConcatenate
NewTable:
Load EntityName,
AssignedEntityName,
if(isnull([Assigned Y/N]),'N',[Assigned Y/N]) as [Assigned Y/N]
Resident NewTableTemp;
drop table NewTableTemp;
Try this,
tab1:
LOAD * INLINE [
EntityName, Position, Color
AA, Top, Blue
BB, Top, Brown
CC, Middle, Red
DD, Bottom, Blue
];
map:
Mapping
LOAD EntityName, 'Y' INLINE [
EntityName, Description
AA,
CC,
];
tab2:
LOAD *, ApplyMap('map',EntityName,'N') As Assigned?
Resident tab1;
Drop Table tab1;
Output: