Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to form the if then statement?

Hello,

I have two attendee lists (2 separate excel files), A and B. I want to create a table that has the people that are on both lists A and B. So if the person is on list A and B, show up in this table. What would the if statement be and how do you do this?

The excel sheet has company, first name, last name, title.

Thank you.

5 Replies
Not applicable
Author

I'm sorry i'm not sure what you mean. Do i do this in the load script? All i want to do is create a table in QV that pulls from both excel files but only shows the people that are on BOTH lists.

swuehl
MVP
MVP

First you need to load your data in, so you need to write some code in your load script, maybe like

Attendees:

LOAD

'TableA' as Source,

Company,

[First  Name],

[Last Name],

Title

FROM TableA.xls

LOAD

'TableB' as Source,

Company,

[First  Name],

[Last Name],

Title

FROM TableB.xls

Make sure you use the correct field names from your excel sheets, and if needed, rename them to common field names. This should result in one large concatenated table, with an additional field Source to tell where the content comes from.

Then, in your chart object, use e.g. [Last Name] and [First Name] as dimensions, and

=if(count(distinct Source)=2, 'BOTH','SINGLE')

as expression to flag attendees that attended both or a single event.

You need to make sure that Names are used consistently between the two tables.

If you still got problems, you could post some sample lines of data (two sample excel sheets to read in) this will help us to see how we could help you.

Regards,

Stefan

rlp
Creator
Creator

You could use the function exists( field, expr).

For that, you need a primary key which identify each client, for example [First Name] & ' ' & [Last Name]. this primary key must be used when charging the two excel files. Moreover, you can so create a List Box which the primary key which enable sophisticated selections on your clients.

Attendees:

LOAD

'TableA' as Source,

Company,

[First  Name],

[Last Name],

[First Name] & ' ' & [Last Name] as ID ,

Title

FROM TableA.xls

LOAD

'TableB' as Source,

Company,

[First  Name],

[Last Name],

[First Name] & ' ' & [Last Name] as ID ,

Title

FROM TableB.xls

After that, you should isolate the ID provided by tableA (and so give them another name) and select only the ID of Table B whose value of ID exists in tableA.

temp_table:

NoConcatenate

LOAD DISCTINCT

     ID as ID_existing_in_A

RESIDENT Attendees

WHERE Source= 'TableA' ;

ID_common :

NoConcatenate

LOAD DISTINCT

     ID ,

     ID as ID_common

RESIDENT Attendees

WHERE

          Source='TableB'

     AND

          exists( ID_existing_in_A , ID )

;

DROP TABLE temp_table;

You then hace a field ID_common satisying your requirements and which propagate selection on the primary key.

senpradip007
Specialist III
Specialist III

Hi,

You can try like following way

Attendees:

LOAD

[First Name] & '-' & [Last Name] as ID ,

Company,

[First  Name],

[Last Name],

Title

FROM TableA.xls

Inner join (Attendees)

LOAD

[First Name] & '-' & [Last Name] as ID ,

Company,

[First  Name],

[Last Name],

Title

FROM TableB.xls

Not applicable
Author

Hope this helps!

Production:
LOAD Field1,
     Field2,
     Field3,
     Field1&Field2&Field3 AS %Key1
FROM
Table1.qvd
(qvd);

Development:

NoConcantenate
LOAD Field1,
     Field2,
     Field3,
     Field1&Field2&Field3AS %Key2
FROM
Table2.qvd
(qvd)
WHERE EXISTS(%Key1,Field1&Field2&Field3)
;
Drop Table Production;