topic Is it a bug? Data not read! in QlikView

Data not read!

Mon, 01 Feb 2010 19:23:49 GMT

Dear All,

I source data through 2 ODBCs from aprox. 100 SQL databases in each and 10 tables in each SQL db using loops within every single ODBCs i.e.

ODBC1

Data:

SQL TABLE 1 "for next " loop DB1 to DB100

SQL TABLE 2 "for next " loop DB1 to DB100

...

SQL TABLE 10 "for next " loop DB1 to DB100

ODBC2

CONCATENATE (Data)

SQL TABLE 1 "for next " loop DB1 to DB100

SQL TABLE 2 "for next " loop DB1 to DB100

...

SQL TABLE 10 "for next " loop DB1 to DB100

When trying to reload data only ODBC1 is read ODBC2 is omitted. When I change the order to ODBC2 ODBC 1 again only first one - ODBC2 - is read.

Is it a bug or am I doing something wrong?

BR,

Przemek

Is it a bug? Data not read!

Wed, 10 Feb 2010 10:48:08 GMT

Can anyone help with this one?

Is it a bug? Data not read!

Miguel_Angel_Baeyens — Wed, 10 Feb 2010 11:08:52 GMT

Although optional, I'd use

Disconnect;

after the first and before the second load to force qlikview to get the new odbc connection.

Is it a bug? Data not read!

Fri, 12 Feb 2010 19:41:05 GMT

Dear Miguel and Micheal,

I've checked the log and the problem is somewhere else. Below you can find the extract from the log starting from disconnecting first odbc. Problem is withloops in the second ODBC - they don't see the upper limit in "FOR" thus don't load the data. In the script I load db names from CDN_Konfiguracja and then make the loops for each table for every db name. Value '-1' in "FOR" is to less the number of loops to be made. Please advise what to do.

Disconnect

ODBC CONNECT*XUserId*XPassword*

DZ_Klienci:

SQL SELECT

Baz_Nazwa AS NazwaKlienta,

CASE WHEN RIGHT(Baz_Dostep,1) = 0 THEN 'Normalny' WHEN RIGHT(Baz_Dostep,1) = 1 THEN 'NT' END AS DostepTyp,

CASE WHEN Baz_Nieaktywna = '0' THEN 'Aktywny' ELSE 'Nieaktywny' END AS "StatusKlienta"

FROM "CDN_Konfiguracja".CDN.Bazy

3 fields found: NazwaKlienta, DostepTyp, StatusKlienta, 193 lines fetched

LET LiczbaKlientow = NoOfRows('DZ_Klienci')

FOR i=0 TO -1

LET LiczbaKlientow = NoOfRows('DZ_Klienci')

FOR i=0 TO -1

Execution finished.

Is it a bug? Data not read!

rwunderlich — Sat, 13 Feb 2010 11:02:57 GMT

Can you confirm by using the table view (Ctl-t) that the first table is being created with the name "DZ_Klienci"?

Can you post the portion of the script that matches the log?

-Rob

Is it a bug? Data not read!

Sat, 13 Feb 2010 12:36:57 GMT

Surprisingly, the table is not created. There are only tables from first ODBC although the script is the same in both ODBC except for the first table name - WR_Klienci and DZ_Klienci . Below is the script:

Disconnect;

ODBC CONNECT TO OPTIMA_DZ (XUserId is LJNFXZFMGB, XPassword is SKeIYQRMBbYIXQVGYRMADYQV) ;

DZ_Klienci:

SQL SELECT

Baz_Nazwa AS NazwaKlienta,

CASE WHEN RIGHT(Baz_Dostep,1) = 0 THEN 'Normalny' WHEN RIGHT(Baz_Dostep,1) = 1 THEN 'NT' END AS DostepTyp,

CASE WHEN Baz_Nieaktywna = '0' THEN 'Aktywny' ELSE 'Nieaktywny' END AS "StatusKlienta"

FROM "CDN_Konfiguracja".CDN.Bazy;

LET LiczbaKlientow = NoOfRows('DZ_Klienci');

FOR i=0 TO $(LiczbaKlientow)-1

LET B = peek('NazwaKlienta',$(i),'DZ_Klienci');

LET C = 'CDN_'& peek('NazwaKlienta',$(i),'DZ_Klienci')& '.CDN.Firma';

SQL SELECT

'$(B)' AS NazwaKlienta,

'Firma' AS Tabela, CASE WHEN Fir_Wartosc = 1 THEN 'EwidencjaRycza?towa' WHEN Fir_Wartosc = 2 THEN 'Ksi?gaPodatkowa' WHEN Fir_Wartosc = 3 THEN 'Ksi?gowo??Kontowa' END AS RodzajKsieg

FROM $(C) WHERE Fir_Numer = 1204;

NEXT

LET LiczbaKlientow = NoOfRows('DZ_Klienci');

FOR i=0 TO $(LiczbaKlientow)-1

LET B = peek('NazwaKlienta',$(i),'DZ_Klienci');

LET C = 'CDN_'& peek('NazwaKlienta',$(i),'DZ_Klienci')& '.CDN.CfgWartosci';

SQL SELECT

'$(B)' AS NazwaKlienta,

CASE WHEN CFW_Wartosc = '0' THEN 'Ten sam miesi?c' WHEN CFW_Wartosc = '1' THEN 'Nast?pny miesi?c' END AS 'Ksi?gowanieSk?adekZus'

FROM $(C) WHERE CFW_CfkId = '37';

NEXT

Is it a bug? Data not read!

Anonymous — Wed, 17 Feb 2010 03:28:32 GMT

Przemek,

There maybe more than one problem here.
If the table "DZ_Klienci" was not created, it is possible that a table with a diffrent name but with the same set of fields was created eralier in the script - so the "DZ_Klienci" was simply autoconcatenated to that eralier table.
If "DZ_Klienci" was not created, then LiczbaKlientow will be 0, hence loop from 0 to -1 which cannot create anything.

Try to use NONCATENATE before "DZ_Klienci", or add some temporary field in this table. See if it helps to move ahead.

Is it a bug? Data not read!

Wed, 17 Feb 2010 18:56:51 GMT

Michael,

I've added an extra field to "DZ_Klienci" and table has been created - attached are table structures views with and without the extra field. What else should I do to make it right?

PS NONCATENATE is not recognised by QV. Is there a mistake in spelling?

BR,

Przemek

Is it a bug? Data not read!

Anonymous — Wed, 17 Feb 2010 19:11:21 GMT

Yes, it was mistyping - NONCONCATENATE.

There are many ways to fix this. I assume you want concatenate, but a little later, to be able to get the count of Rows in this table. A possible solution is to load first with this additional TEST field, as DZ_Klienci_temp, and later in the script concatenate it to the table WR_Klienci but without the TEST field:


CONCATENATE (WR_Klienci) LOAD
 NazwaKlienta,
 DostepTyp,
 StatusKlienta
RESIDENT DZ_Klienci_temp;
DROP TABLE DZ_Klienci_temp;

Is it a bug? Data not read!

Tue, 23 Feb 2010 08:35:26 GMT

Iv'e changed the script as below and it didn't work - records from DZ_Klienci_temp are not in the data. As there many ways to fix this is there one that doesn't require using the extra field and another table? The original report is much more complexed thus I'd like to use solutions that are as simple as possible.

Thank you, Przemek.

DZ_Klienci_temp:

SQL SELECT

Baz_Nazwa AS NazwaKlienta,

CASE WHEN RIGHT(Baz_Dostep,1) = 0 THEN 'Normalny' WHEN RIGHT(Baz_Dostep,1) = 1 THEN 'NT' END AS DostepTyp,

CASE WHEN Baz_Nieaktywna = '0' THEN 'Aktywny' ELSE 'Nieaktywny' END AS "StatusKlienta",

'Test' AS Test

FROM "CDN_Konfiguracja".CDN.Bazy;

CONCATENATE (WR_Klienci) LOAD

NazwaKlienta,

DostepTyp,

StatusKlienta

RESIDENT DZ_Klienci_temp;

DROP TABLE DZ_Klienci_temp;

//RodzajKsi?gowo?ci

LET LiczbaKlientow = NoOfRows('DZ_Klienci_temp');

FOR i=0 TO $(LiczbaKlientow)-1

LET B = peek('NazwaKlienta',$(i),'DZ_Klienci_temp');

LET C = 'CDN_'& peek('NazwaKlienta',$(i),'DZ_Klienci_temp')& '.CDN.Firma';

SQL SELECT

'$(B)' AS NazwaKlienta,

'Firma' AS Tabela, CASE WHEN Fir_Wartosc = 1 THEN 'EwidencjaRycza?towa' WHEN Fir_Wartosc = 2 THEN 'Ksi?gaPodatkowa' WHEN Fir_Wartosc = 3 THEN 'Ksi?gowo??Kontowa' END AS RodzajKsieg

FROM $(C) WHERE Fir_Numer = 1204;

Is it a bug? Data not read!

Anonymous — Sun, 07 Mar 2010 19:44:13 GMT

Hi Przemek,

Maybe you've resolved your question by now, but anyway...
When I said that there could be many solutions, I didn't promise that there could be always a simple solution. Often you have to use more or less complex approach.
I cannot give you a specific adivice because it requires understanding of the specifics of your situation. Forum is not the best place for an involbved consulting process.
I can tell though that there is at least one essential error here. Notice that you are trying to get the number of rows from the table that has been dropped:
LET LiczbaKlientow = NoOfRows('DZ_Klienci_temp');
Apparently all actions after this do not produce any result.

Is it a bug? Data not read!

Fri, 12 Mar 2010 15:09:14 GMT

Hello Michael,

Unfortunatelly, cutting off the drop part didn't help. I'll educate myself a bit more and be back to the topic in the future. Thank you.

BR,
Przemek

Is it a bug? Data not read!

Anonymous — Sun, 14 Mar 2010 18:57:23 GMT

I suspect that what you're trying to do is rather simple, but I can't help without good understanding the structure of the data (multiple databases, tables of identical structure). My guess is this structure was created to make the reporting easier. Never a good idea for QlikView. If you have authority for this - do not use this structure and load QV app from the raw data.