Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.

Tags (1)
5 Replies
Not applicable

Re: How to form the if then statement?

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.

MVP
MVP

Re: How to form the if then statement?

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

richard_pressan
Contributor

Re: How to form the if then statement?

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
Valued Contributor III

Re: How to form the if then statement?

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

Re: How to form the if then statement?

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;

Community Browser