Data not read!

[#1]

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

[#2]

Can anyone help with this one?

[Miguel_Angel_Baeyens]

Although optional, I'd use

Disconnect;

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

[#4]

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.

[rwunderlich]

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

[#6]

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

[Anonymous]

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.

[#8]

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

[Anonymous]

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;

[#10]

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;

NEXT