Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
CreepyCatLady
Contributor III
Contributor III

Create table from data from two other tables

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):

CreepyCatLady_0-1614112367894.png

Table2 (Loaded from dataset):

CreepyCatLady_1-1614112401225.png

Desired Table (Created in script):

CreepyCatLady_2-1614112425991.png

 

6 Replies
saran7de
Master
Master

Please add some sample data and expected output for faster response

GaryGiles
Specialist
Specialist

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;

CreepyCatLady
Contributor III
Contributor III
Author

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.

GaryGiles
Specialist
Specialist

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;

saran7de
Master
Master

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;
saran7de
Master
Master

Output:

commQV17.PNG