2 Replies Latest reply: Aug 15, 2013 2:11 PM by Nevin Kumar RSS

    Retrieve/Get rows from excel on condition... Please help!!!

      Experts,

       

      I am trying to get GLOSSARY items from EXCEL spreasheet and populate it in the dashboard. Excel sheet contains 3 columns, IDENTIFIER, ITEM A and ITEM B

      I have to pull the data in the rows based on the condition or text available in IDENTIFIER column,

       

      ie If IDENTIFIER = BSA DRY RUN then POPULATE data from the rows start with REGION and ADDRESS

      ie If IDENTIFIER = NBN DRY RUN then POPULATE data from the rows start with CONSENT and ADDRESS

       

      Below is the excel spreadsheet data, Please let me know how to do this? If so, PLEASE SHARE THE SCRIPT as well.

       

      IDENTIFIER         ItemA                    ItemB

       

       

      BSA DRY RUN     BSA DRY RUN REPORT:     THIS REPORT HELPS BUSINESS USERS

      NBN DRY RUN     NBN DRY RUN REPORT:     THIS REPORT HELPS END USERS

      REGION          REGION.REGIONID          REGION ID OF THE CUSTOMER

      REGION          REGION.REGION_NAME      NAME OF THE REGION

      ADDRESS         ADDRESS.PAYLOAD_ID       CLIENT UNIQUE ID

      ADDRESS         ADDRESS.BATCH_ID         BATCH ID

      CONSENT         CONSENT.CIDN                CUSTOMER ID

      CONSENT         CONSENT.CIDN_DESCRIPTION      CIDN DESCRPTION

       

       

       

        • Re: Retrieve/Get rows from excel on condition... Please help!!!

          Hi new2Qlik, on which field do you want to filter the 2nd condition ("data starts with...")?

          Assuming it's on ItemA then use this:

           

          LOAD * From Excel

          Where IDENTIFIER = 'BSA DRY RUN'

          and (ItemA Like 'REGION*' or ItemA like 'ADDRESS*')

           

          Concatenate

          LOAD * From Excel

          Where IDENTIFIER = 'NBN DRY RUN'

          and (ItemA Like 'CONSENT*' or ItemA like 'ADDRESS*')

            • Re: Retrieve/Get rows from excel on condition... Please help!!!

              Thank you Ori Tal,

              I would like to pull data from both ITEM A and ITEM B for the same row. The script above would fetch data from ITEM A column and not from ITEM B.  I also dont have any common data in ITEM B..

              Also, the above script would concatenate both data .. Am i right?.. I will have either of one condition to be active for a report .. I want to have the data populated in LIST box in dashboard for the above conditions in which either of one condition will be active based on the report i create...I am new to learn this tool and my question may appear silly..  Could you please advise more on this...