5 Replies Latest reply: May 26, 2011 9:38 AM by eliton3d RSS
      • How do I turn some text into a variable and bind as the primary key to another table?
        Martin Pohl

        Hello,

         

        as I can see the table you select from the ODBC is a cross table? Dimensions like AGENCIA, BDU,...NOMEREDE and then it looks like sum values for the months.

        So select the datas, make a cross table load an use right(fieldname,3) to identify the month-name.

        After that you can match them with your inlinetable.

        Regards

        • How do I turn some text into a variable and bind as the primary key to another table?

          I think it's more a SQL problem than a QlikView one.

           

          Which db are you on? On MS Sql Server, you would use a UNPIVOT operator to turn columns in rows of data.

           

          Or a JOIN to obtain the same effect:

           

          SELECT AGENCIA,

               MONTH,

               SomaDeACT

          FROM (

               SELECT AGENCIA,

                    Months.MONTH,

                    CASE Months.MONTH

                         WHEN 'GEN' THEN SomaDeACT_GEN

                         WHEN 'FEB' THEN SomaDeACT_FEB

                         WHEN 'MAR' THEN SomaDeACT_MAR

                         ...

                         WHEN 'DEC' THEN SomaDeACT_DEC

                    END AS SomaDeACT

               FROM YourTable,

                    (SELECT 'GEN' AS MONTH

                    UNION ALL SELECT 'FEB'

                    UNION ALL SELECT 'MAR'

                    ...

                    UNION ALL SELECT 'DEC'

                    ) AS Months) AS D

          WHERE SomaDeACT IS NOT NULL;

           

          You join each row in your source table with 12 values, 1 for each month (The Select...Union part).

          Then use a case to extract, for each month, the right column.

           

          This should do the trick, with minimal variations, on many RDBMS.

           

          Have a nice day!

            • Re: How do I turn some text into a variable and bind as the primary key to another table?

              SQL Error:[Microsoft][Driver ODBC para Microsoft Access] Erro de sintaxe (operador faltando) na expressão de consulta 'CASE Months.MONTH           WHEN 'JAN' THEN 'SomaDeACT_JAN'  WHEN 'FEB' THEN 'SomaDeACT_FEB'  WHEN 'MAR' THEN 'SomaDeACT_MAR'  WHEN 'APR' THEN 'SomaDeACT_APR'  WHEN 'MAY' THEN 'SomaDeACT_MAY'  WHEN 'JUN' THEN 'SomaDeACT_JUN'  WHEN 'JUL' THEN 'SomaDeACT_JUL' '.

              SQL Scriptline:

              SQL State:37000

               

              ODBC CONNECT TO [Banco de dados do MS Access; DBQ=Y:\2011\DESPESAS\FECHAMENTO MENSAL\BASES MACRO\ACT ROP Cognos.mdb];

              SELECT AGENCIA,

              BDU,

              `DESC_CONTA_CTB`,

              `LINHA_DESPESA`,

              NOMEGDN,

              NOMEREDE,

              MONTH,

              SomaDeACT

              FROM (

              SELECT AGENCIA,

              Months.MONTH,

              CASE Months.MONTH        

              WHEN 'JAN' THEN 'SomaDeACT_JAN'

              WHEN 'FEB' THEN 'SomaDeACT_FEB'

              WHEN 'MAR' THEN 'SomaDeACT_MAR'

              WHEN 'APR' THEN 'SomaDeACT_APR'

              WHEN 'MAY' THEN 'SomaDeACT_MAY'

              WHEN 'JUN' THEN 'SomaDeACT_JUN'

              WHEN 'JUL' THEN 'SomaDeACT_JUL'

              WHEN 'AUG' THEN 'SomaDeACT_AUG'

              WHEN 'SEP' THEN 'SomaDeACT_SEP'

              WHEN 'OCT' THEN 'SomaDeACT_OCT'

              WHEN 'NOV' THEN 'SomaDeACT_NOV'

              WHEN 'DEC' THEN 'SomaDeACT_DEC'

              END AS SomaDeACT

              FROM Consulta,

              (SELECT 'JAN' AS MONTH

              UNION ALL SELECT 'FEB'

              UNION ALL SELECT 'MAR'

              UNION ALL SELECT 'APR'

              UNION ALL SELECT 'MAR'

              UNION ALL SELECT 'MAY'

              UNION ALL SELECT 'JUN'

              UNION ALL SELECT 'JUL'

              UNION ALL SELECT 'AUG'

              UNION ALL SELECT 'SEP'

              UNION ALL SELECT 'OCT'

              UNION ALL SELECT 'NOV'

              UNION ALL SELECT 'DEC'

              ) AS Months) AS D

              WHERE SomaDeACT IS NOT NULL;

                • How do I turn some text into a variable and bind as the primary key to another table?

                  If you're under Access, you don't have the CASE construct.

                  You should probably use a Switch function.

                   

                  Look here:

                  http://www.techonthenet.com/access/functions/advanced/switch.php

                   

                  Instead of:

                   

                  CASE Months.MONTH        

                  WHEN 'JAN' THEN 'SomaDeACT_JAN'

                  WHEN 'FEB' THEN 'SomaDeACT_FEB'

                  WHEN 'MAR' THEN 'SomaDeACT_MAR'

                  WHEN 'APR' THEN 'SomaDeACT_APR'

                  WHEN 'MAY' THEN 'SomaDeACT_MAY'

                  WHEN 'JUN' THEN 'SomaDeACT_JUN'

                  WHEN 'JUL' THEN 'SomaDeACT_JUL'

                  WHEN 'AUG' THEN 'SomaDeACT_AUG'

                  WHEN 'SEP' THEN 'SomaDeACT_SEP'

                  WHEN 'OCT' THEN 'SomaDeACT_OCT'

                  WHEN 'NOV' THEN 'SomaDeACT_NOV'

                  WHEN 'DEC' THEN 'SomaDeACT_DEC'

                  END AS SomaDeACT

                   

                  Write:

                  Switch(Month = 'JAN', SomaDeACT_JAN, Month = 'Feb', SomaDeACT_FEB, ..., Month='DEC', SomaDeACT_DEC) as SomaDeACT

                   

                  And remember: you can't SELECT fields you don't have in the subquery:

                  SELECT AGENCIA,

                  BDU,

                  `DESC_CONTA_CTB`,

                  `LINHA_DESPESA`,

                  NOMEGDN,

                  NOMEREDE,

                  MONTH,

                  SomaDeACT

                   

                  BDU, DESC_CONTA_CTB, LINHA_DESPESA, NOMEGDN and NOMEREDE are not in the subquery, so you can't query for them. Either use 2 load statements or add them into the Subquery, right before the switch statement I just posted.

                   

                  I don't have Access to test if it works. But, really, you should understand what's going on.

                   

                  Hope it helps.