Second value in variable IS NULL

[jjordaan]

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

[jagannalla · Accepted solution]

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

[jjordaan]

Jagan,

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