Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
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;