Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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

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"

Duvida.PNG

5 Replies
Partner
Partner

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

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

Not applicable

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!

Not applicable

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;

Not applicable

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.

Not applicable

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

I understood. Not a simple question as I thought.

Chiesa thank you for your reply.

You are so cool.