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

[#1]

How do I turn some text into a variable and bind as the primary key to another table?
For example.
I have a table where the month and are in line with other information. I need to separate the month and binds it to the table "Month"

[martinpohl]

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

[#3]

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!

[#4]

chiesa80 thanks for your reply. but I could not run the query.
the following error appears:

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

how do I fix?

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;

[#5]

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.

[#6]

I understood. Not a simple question as I thought.

Chiesa thank you for your reply.

You are so cool.