Test each record of a table with each record of another table

[christopheschel]

Hello, I have been looking for a while for a solution to prevent having to hardcode, the content of a table, into nested IF's. 2 files were added. 1 with the normal functioning hardcoded nested IF solution and the other with the loop which doesn't work. The second file was written in order to prevent having to hardcode every time again. Because the table, whose values were hardcoded, has changing both values and total number of records. The 2nd file will not be correct. The ‘Error’ table has a field containing the numerical date of each ‘error’ record being ‘DateError’. The table ‘Upgrade_test’ has 2 fields containing the numerical beginning date and ending date of a test after an Upgrade was executed. The value of both numerical dates begins with 42xxx  or 43xxx. The intention is to check whether or not errors were made during the period of testing of an upgrade. The type of error is not important.  If an error is made during an Upgrade test period, the content for field ‘ErrorOrNotField’ has to become number ‘1’ (if not so, the number is to be ‘0’ in the Loop alternative). This field is used just for this purpose.

In the file containing the loop, 2 alternatives are added. 1 is comment by ‘//’ and the other not. 1 option with an if -test (((DateError >= $(BeginDatUpgradeVAR)) and (DateError <= $(EndDatUpgradeVAR)), 1, 0) in the loop itself. And the other option has the same test in the ‘where’-clause.

I Assume that one of the reasons why the ‘LOOP’-code is not working is because ‘As’ is repeated while several loads are repeated.

Could someone help me out please? All recommendations are welcome. Thx in advance, Christopher

[marcus_sommer]

I think I would use rather an approach like this one:

m: mapping load Begin_date + iterno() - 1 as Lookup, 1 as Return
from Upgrades_QVDDB.qvd (qvd) while Begin_date + iterno() - 1 <= End_date;

f: load *, applymap('m', DateError, 0) as Flag resident facts;

- Marcus

[christopheschel]

Thx a lot for the help,

Having added your solution to the project, it doesn’t yield the required help. 

The provided solution doesn’t work because:

• Each record of the “UpgradeTestTable” is to be tested with each record of the “ErrorTable”, which is not the case. In other words, it is important to know whether or not the records of the “ErrorTable” are registered during the periods of the “UpgradeTestTable”. The latter is provided with defined by the beginning and ending dates in this table.

 The tests to be performed are not linked to installed upgrades. If an upgrade occurs, a number is to be found in the UpgradeNr field. Otherwise the field in the ErrorTable contains a 0.

Screenshots of the excel DB were inserted in a Word doc. Also the code in *.docx format  is attached. The hardcoded way works fine. But in practice an other method is desired. A project with 2 dashboards is added. The result of the first one is correct because it is based on the hardcoded alternative.

Can somebody help me? Thx in advance

[marcus_sommer]

Please provide the excel-file (maybe with a reduced number of records and only the needed columns and with some kind of scrambling in the case that the data are sensitive in some way).

- Marcus

[christopheschel]

Hello Marcus, 

The excel DB with tables concerned is attached.

Kind regards, Christopher

[marcus_sommer]

Both methods return the same results - you need of course to ensure that the while-loop worked and maybe adjusting some of the formattings respectively to convert them into numbers, for example I had to use date#() because DD/MM/YYYY isn't my default date-format:

- Marcus

[christopheschel]

Thx a lot Marcus,

I am going to examine your solution in order to  learn from it.

kind regards,Christopher