5 Replies Latest reply: Aug 7, 2012 9:39 AM by At titude RSS

    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.

        • 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.

            • Re: How to form the if then statement?
              Stefan Wühl

              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?
              Richard Pressanti

              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?
                Pradip Sen

                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?

                  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;