topic Re: Second value in variable IS NULL in QlikView

Second value in variable IS NULL

jjordaan — Thu, 08 Nov 2012 10:13:20 GMT

Hi QlikView addicts,

I created a simple loop to load some QVD files from a certain directory.

With a QVD Loader I created QVD files with a prefix the databasename, companyname and then the tabelname.

For example a have 2 QVD files TH_ST057$IKANO, Bangkok Store$Item.qvd (TH_ST057 is the database name, IKANO, Bangkok Store is the store name and Item is the tabel name). The second QVD files is almost the same, the only difference is that the database name is different. So the full name of the second QVD is TH_ST200$IKANO, Bangkok Store$Item.qvd.

I'm using the following loop;

Stores:

LOAD * INLINE

[

DatabaseID, DatabaseName, CompanyName, CompanyPrefix

1, TH_ST057, 'IKANO, Bangkok Store', 057BK

2, TH_ST200, 'IKANO, Bangkok Store', 200BK

];

LET RowCompanies = NoOfRows('Stores');

FOR i = 1 to $(RowCompanies)

LET vDB = FieldValue('DatabaseName', $(i));

LET vStore = FieldValue('CompanyName', $(i));

LET vRowPrefix = FieldValue('CompanyPrefix', $(i));

TRACE i: $(i);

Item:

LOAD

'$(vRowPrefix)' AS ItemPrefix,

'$(vRowPrefix)_' & No_ AS ItemNo,

Description

FROM

[$(vDirQVD)$(vDB)$$(vStore)$Item.qvd] (qvd);

NEXT;

The problem that I have is when the loop comes at the second QVD the variable vStore is not set with the value 'IKANO, Bangkok Store' but with the value NULL and the QVD could not be found.
The strange thing is that the first loop (the load of the first QVD) is going oke.

I hope somebody can help me.

Thanks in advance

Re: Second value in variable IS NULL

jagannalla — Thu, 08 Nov 2012 10:44:11 GMT

Hello,

Instead of FieldValue use Peek function. B'coz FieldValue will hold unique values of the columns. If you observe 'IKANO, Bangkok Store' is repeating two times in CompanyName. When it is reading the second record of CompanyName it will not hold the repeated data.

Change the code according to below:

Stores:
LOAD * INLINE
[
    DatabaseID, DatabaseName, CompanyName, CompanyPrefix
    1, TH_ST057, 'IKANO, Bangkok Store', 057BK
    2, TH_ST200, 'IKANO, Bangkok Store', 200BK
];
LET RowCompanies          = NoOfRows('Stores');
FOR i= 0 to $(RowCompanies)-1
LET vDB = Peek('DatabaseName',$(i),'Stores');
LET vStore = Peek('CompanyName',$(i),'Stores');
LET vRowPrefix = Peek('CompanyPrefix',$(i),'Stores');
TRACE i: $(i);
Item:
LOAD
          '$(vRowPrefix)' AS ItemPrefix,
          '$(vRowPrefix)_' & No_ AS ItemNo,
          Description
FROM
[$(vDirQVD)$(vDB)$$(vStore)$Item.qvd] (qvd);
NEXT;

Hope it helps you.

Cheers!!

Jagan Nalla

Re: Second value in variable IS NULL

jjordaan — Thu, 08 Nov 2012 10:58:29 GMT

Jagan,

Thank you for your help I didn't know this about the function FieldValue.