19 Replies Latest reply: Dec 4, 2015 9:49 AM by George Evangelou RSS

    Applymap and tmp table

    George Evangelou

      Hi all.

      I use applymap to create a tmp table.

      Then i want to filter that data using select statements.

      I dont know how to replace Transactions table with my tmp table!!!

      Pls help.

       

      Below is a sample of my script:

       

      //Transfer user names to Transactions

      MapTelephoneExtToUsers:

      Mapping

      LOAD * FROM

      [New folder\New folder\USERS.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      //Create temp table containing all fields

      TransactionUsers:

      Load *,

      ApplyMap ('MapTelephoneExtToUsers',Ext, null() ) as NUsers;

      sql select  * from Transactions;

       

      MinMaxTable:

      Load *;

      SQL SELECT MIN(DATE) AS minDate, MAX(DATE) AS maxDate from Transactions;

      let vMinDate = peek('minDate',0,'MinMaxTable');

      let vMaxDate = peek('maxDate',0,'MinMaxTable');

       

      Q1_TOTAL_TRANSACTIONS_NUMBER:

      SELECT ext, COUNT(ext) AS INTERNATIONAL FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

      Outer Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

      LOAD *;

      SELECT ext, COUNT(ext) AS MOBILE        FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '9%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

        • Re: Applymap and tmp table
          Ruben Marin

          Hi George, maybe is only a typo in the post but your variables are named 'vMinDate' and 'vMaxDate', in the SELECT you're using 'Mindate' and 'MaxDate' (without the preceding 'v').

           

          Also check in the reload log how is constructing the final SELECT, maybe the variables needs a Date() funtion to format the date.

          • Re: Applymap and tmp table
            Tamil Nagaraj

            Hi George,

             

            Check the below link. You might get some idea.

             

            http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

            • Re: Applymap and tmp table
              George Evangelou

              Ok i will take things from the start because i haven't explained well what i need .

              I have a table named "Transactions" and my script works perfectly when i am using it.

              All i want to do now is add a column to that table using applymap and use the new table in my script.

              Ex. I have now:

              Q1_TOTAL_TRANSACTIONS_NUMBER:

              SELECT ext, COUNT(ext) AS INTERNATIONAL FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

               

              And i want to do:

              Q1_TOTAL_TRANSACTIONS_NUMBER:

              SELECT ext, COUNT(ext) AS INTERNATIONAL FROM TransactionUsers WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

                • Re: Applymap and tmp table
                  Ruben Marin

                  Have you tried with Resident like I said before?

                  Q1_TOTAL_TRANSACTIONS_NUMBER:

                  LOAD ext, COUNT(ext) AS INTERNATIONAL

                  Resident TransactionUsers

                  WHERE DateField>='$(vMindate)' and DateField<='$(vMaxDate)' AND ( Left(TRIM(number_dialed), 2)='+_' OR Left(TRIM(number_dialed), 2)='0_') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

                   

                  All the fields used in 'Where' have to be loaded in TransactionUsers table.

                • Re: Applymap and tmp table
                  George Evangelou

                  i have tried the script you sent me but it gives me the following error:

                   

                  Field not found - <date>

                  MinMaxTable:

                  LOAD * Resident TransactionUsers Where date>='' and date<='18/11/2015'

                    • Re: Applymap and tmp table
                      Tatsiana Chetirbok

                      Hello!

                       

                      try this script

                       

                      //Create temp table containing all fields

                      TransactionUsers:

                      Load *,

                      ApplyMap ('MapTelephoneExtToUsers',Ext, null() ) as NUsers;

                      sql select  * from Transactions;

                       

                      MinMaxTable:

                      Load MIN(DATE) AS minDate, MAX(DATE) AS maxDate Resident TransactionUsers;

                      let vMinDate = peek('minDate',0,'MinMaxTable');

                      let vMaxDate = peek('maxDate',0,'MinMaxTable');

                       

                      noconcatenate:

                      Q1_TOTAL_TRANSACTIONS_NUMBER:

                      load ext, COUNT(ext) AS INTERNATIONAL Resident TransactionUsers

                      WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '+*' OR TRIM(number_dialed) LIKE '0*') AND status = 'O' AND cost <> 0.0

                      GROUP BY ext;

                      Outer Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

                      load ext, COUNT(ext) AS MOBILE Resident TransactionUsers

                      WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '9*') AND status = 'O' AND cost <> 0.0

                      GROUP BY ext;

                      drop table TransactionUsers;

                      • Re: Applymap and tmp table
                        Ruben Marin

                        It's telling that the 'date' field doesn't exists, check if this is exactly the name of the field. Field name are case sensitive so 'date'<>'Date'<>'DATE'

                      • Re: Applymap and tmp table
                        George Evangelou

                        Hi Ruben thanks for all the help. You were wright about the field,it was case sensitive. Now my problem is when i run the script it gives no errors but it doesn't fetch a line.

                        Ex:

                        Connected

                        MapTelephoneExtToUsers << Sheet1 76 lines fetched

                        TransactionUsers << Transactions 289.470 lines fetched

                        MinMaxTable << TransactionUsers 1 lines fetched

                        Q1_TOTAL_TRANSACTIONS_NUMBER << TransactionUsers 0 lines fetched

                        TransactionUsers 0 lines fetched

                         

                         

                        Below is my script:

                        MapTelephoneExtToUsers:

                        Mapping

                        LOAD *   

                        FROM

                        [qliktest\New folder\USERS.xlsx]

                        (ooxml, embedded labels, table is Sheet1);

                         

                        TransactionUsers:

                        Load *,

                        ApplyMap ('MapTelephoneExtToUsers',Ext, null() ) as NUsers;

                        sql select  * from Transactions;

                         

                        MinMaxTable:

                        LOAD MIN(Date) AS minDate, MAX(Date) AS maxDate Resident TransactionUsers;

                        let vMinDate = peek('minDate',0,'MinMaxTable');

                        let vMaxDate = peek('maxDate',0,'MinMaxTable');

                         

                        Q1_TOTAL_TRANSACTIONS_NUMBER:

                        LOAD Ext, COUNT(Ext) AS INTERNATIONAL

                        Resident TransactionUsers

                        WHERE Date>=$(MinDate) AND Date<=$(MaxDate) AND  ( Left(TRIM(Number_Dialed), 2)='+_' OR Left(TRIM(Number_Dialed), 2)='0_') AND Status = 'O' AND Cost <> 0.0 GROUP BY Ext;

                         

                        Outer Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

                         

                        LOAD Ext, COUNT(Ext) AS MOBILE

                        Resident TransactionUsers

                        WHERE Date>=$(MinDate) AND Date<=$(MaxDate) AND ( TRIM(Number_Dialed) LIKE '9%') AND Status = 'O' AND Cost <> 0.0 GROUP BY Ext;

                         

                        MinDate and MaxDate get whatever value i choose in the calendar so their working proberly, the problem occurs when i try to filter the Resident table

                          • Re: Applymap and tmp table
                            Tatsiana Chetirbok

                            you should use '*' in your conditions instead of '%'

                            • Re: Applymap and tmp table
                              Staffan Johansson

                              Hi,

                               

                              Mapping Load takes two fields and only two, you have to define the keyfiled and the field you want to map, like this.

                              MapTelephoneExtToUsers:

                              Mapping

                              LOAD

                              Keyfield,

                              Valuefield  

                              FROM

                              [qliktest\New folder\USERS.xlsx]

                              (ooxml, embedded labels, table is Sheet1);

                              • Re: Applymap and tmp table
                                Ruben Marin

                                Hi, in your '

                                - $(MinDate) --> '$(MinDate)'

                                - $(MaxDate) --> '$(MaxDate)'

                                - (TRIM(Number_Dialed) LIKE '9%') --> (Left(TRIM(Number_Dialed),1)='9')

                                  • Re: Applymap and tmp table
                                    George Evangelou

                                    MinDate and MaxDate variables get whatever value i select in the calendar of qlikview so their working correctly. I tested them many times.

                                     

                                    I also tried the syntax you recommended in your last post but the problem still remains the same.

                                     

                                    The difference from previously is that now i filter the resident table (which is Transaction table + one extra column) instead Transactions table.

                                    BEFORE:

                                    SELECT ext, COUNT(ext) AS INTERNATIONAL FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

                                    For the date 13/11/2015 it fetches 27 lines.

                                    NOW:

                                    LOAD Ext, COUNT(Ext) AS INTERNATIONAL

                                    Resident TransactionUsers

                                    WHERE Date>='$(MinDate)' AND Date<='$(MaxDate)' AND  ( Left(TRIM(Number_Dialed), 2)='+_' OR Left(TRIM(Number_Dialed), 2)='0_') AND Status = 'O' AND Cost <> 0.0 GROUP BY Ext;

                                    For the date 13/11/2015 it fetches 0 lines.



                                    "" ( Left(TRIM(Number_Dialed), 2)='+_' OR Left(TRIM(Number_Dialed), 2)='0_') ""

                                    When i remove the script above it fetches 40 lines. So the problem must be in that part of the script.

                                      • Re: Applymap and tmp table
                                        Tatsiana Chetirbok

                                        Hi, try this

                                         

                                        Q1_TOTAL_TRANSACTIONS_NUMBER:

                                        load ext, COUNT(ext) AS INTERNATIONAL Resident TransactionUsers

                                        WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '+*' OR TRIM(number_dialed) LIKE '0*') AND status = 'O' AND cost <> 0.0

                                        GROUP BY ext;

                                        Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

                                        load ext, COUNT(ext) AS MOBILE Resident TransactionUsers

                                        WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '9*') AND status = 'O' AND cost <> 0.0

                                        GROUP BY ext;

                                        • Re: Applymap and tmp table
                                          Ruben Marin

                                          If it's only that line check if there is records in TransactionUsers with values in Number_Dialed starting by '+_' or '0_'.

                                           

                                          you can also try wildmatch:

                                          Where ... and WildMatch(Trim(Number_Dialed), '+_*', '0_*') and ...

                                    • Re: Applymap and tmp table
                                      George Evangelou

                                      Thank you all for your help. The problem is fixed.