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

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

Thu, 19 May 2011 12:53:46 GMT

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"

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

martinpohl — Thu, 19 May 2011 16:01:47 GMT

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?

Thu, 19 May 2011 16:25:33 GMT

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?

Thu, 19 May 2011 17:40:19 GMT

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;

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

Fri, 20 May 2011 13:02:53 GMT

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.

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

Thu, 26 May 2011 13:38:23 GMT

I understood. Not a simple question as I thought.

Chiesa thank you for your reply.

You are so cool.