topic Re: How to form the if then statement? in QlikView

How to form the if then statement?

Fri, 03 Aug 2012 15:48:49 GMT

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.

Re: How to form the if then statement?

Fri, 03 Aug 2012 16:18:01 GMT

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.

Re: How to form the if then statement?

swuehl — Fri, 03 Aug 2012 17:11:22 GMT

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

Re: How to form the if then statement?

rlp — Mon, 06 Aug 2012 08:39:49 GMT

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.

Re: How to form the if then statement?

senpradip007 — Mon, 06 Aug 2012 09:39:48 GMT

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

Re: How to form the if then statement?

Tue, 07 Aug 2012 13:39:27 GMT

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;