?Complicated? logical problem - plz help

[datanibbler]

Hi,

I am trying to solve the following logical problem:

- I have a lot of storage_areas with names like "KT1", "KT2" ... up to "KT75" and "KT101" up to "KT175".

- In addition to those, there are areas with names like "KT1OUT", "KT2OUT" etc. up to "KT75OUT" and "KT101OUT" up to

   "KT175OUT".

- The areas "KT1" and "KT101", however, are actually just one, the names are just used for different purposes.

- Likewise, "KT1OUT" and "KT101OUT" are one unit.

- Bookings (of goods into the database) can occur on both "KT1" and "KT101" and so on.

- I already have a table (a "database" I'm just building up in Excel) with a number of shelves we have here.

=> I would like to concatenate the list of these to it, but I cannot think of the right way to do this:

   - If I concatenate the entire list, I would end up with double the storage_space as KT1 and KT101, which are actually just one area

     with 5 places, would end up with twice that.

   - If I do a mapping (in Excel) that would assign the name "KT1" to both KT1 and KT101 and I load that with a DISTINCT, the amount

      of storage_space would be correct, but I would potentially lose any items booked on KT101.

I hope you can follow me so far. It is complicated enough. I needed a while to understand it, and even now I'm not so sure ...

One possible solution I could come up with is, I have to assign the storage_areas I have in the "real" database a new name, which would make all items which were booked on KT101 appear as booked on KT1 and so on. Then I could do that with the LOAD DISTINCT and I would not lose out on any items.

Maybe, however, someone has a better idea? Any suggestions are welcome.

[marcus_sommer]

Is there a DISTINCT statement in one of these loads? By matching tables per concatenate or join/keep distinct has an effect of all involved tables.

[#3]

Can you provide more info please. Which distinct fields are you loading?

[datanibbler]

Hi Marcus,

yep, there is.

I have found out that the error is in the other part: The tables I have loaded so far have only 55 records (with a COUNT on the GUI). I have no idea so far why the Debugger tells me there are 69 - but then, it also keeps telling me the other table still has 300 even with the DISTINCT ...

So, 55+150, that should be 205. So I only need to find out where this one blank line comes from ...

[marcus_sommer · Accepted solution]

Has this blank-line no recno/rowno? Helpful is often also to use something like:

'I am from the first table load' as DataSource

and appropriate for all involved loadings.

[datanibbler]

Hi Marcus,

those are good hints that I'l use next time. For this time, it's solved - I just equipped my first LOAD with a WHERE NOT(ISNULL()) clause - I guess that should always be done with Excel files - and my first table has exactly the 55 records it should have as when I just count them in the Excel file itself.

In the end - when I concatenate the second DISTINCT LOAD - I end up with just the 205 records I expected.

So, this problem is solved - I think I'll take my lunchbreak now before I proceed to the next one 😉

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi Marcus,

now I am facing the next problem:

I have a straight_table chart showing data about all my areas (the areas are listed with some attributes in my Excel file, but I link that to a table from the database to get some info from there)

=> The chart should show only those of the storage areas in my Excel file which link to some data in the database_table.

=> My problem is with some of those KT areas - they ALL show up in the chart, but only some seem

     to have some linked data in the other table from the database. Some do not - and indeed, I have tested two or

     three and found that they are not present in the table from the database.

(one example: Area KT68 is of course listed in my Excel file since it exists, but it is not used right now, so it is not present in the database_table, nor is KT168 - which should not exist after the Applymap().

Another still stranger example: Area KT55 of course exists in my Excel listing - it also exists in the database_table, so it is in use - but they are not linked. It shows up, but it does not show the linked data.

The key_field is one of my dimensions for the chart.

The strange thing is - the names of those areas all start with KT, so I assume they are all STRING in type - still, some do show some linked data from the database_table and some don't

Do you have an idea what could be the reason for this? I don't, but I'll hang on.

[marcus_sommer]

If some links fail even there are values on both sides it's most probably that the values aren really identically. You might need again check on len() and the included characters and maybe clean them with trim() or keepchar(9 or similar - also formattings might be possible in this way that the field contained numeric values and numberformat might be something like "KT ###".

The other topic of missing links is more difficult - if I couldn't solve the case by concatenating the tables instead of a join or a table-association within datamodel I have them often to create with something like this:

xls:

Load *, 'xls' as Source From xls;

db:

Load *, 'db' as Source, 'available' as DataTyp From db;

     concatenate

Load xlsKEY , 'db' as Source, 'missing' as DataTyp Resident xls where not exists(dbKEY, xlsKEY);

I'm not sure if it's exactly suitable in your case - but the meaning is to handle the NULL in any way.

- Marcus

[datanibbler]

I think I have found the reason.

I have, in that table from the database, a formula to build the key_field using a set of several expressions to choose from (the formatting of names is different in different areas) - I had that formula before using the Applymap() - so that the key_field still knew the KT areas KT155, KT168 or KT101 - but the link was supposed to work on only one of the two - so the link would have worked if in both tables, there had been a KT5 or the like - but if there was a booking in the database_table on KT105, the key_field in the database_table would have been KT105, the key_field in my Excel would have been KT5 ...

Let's see if that was it.

[datanibbler]

Nope - I solved half of the problem. Better than naught, but not there yet.

I don't have cases anymore that the link fails because my list knows only KT55, not KT155, but there are bookings on KT155 and the link_field does not match the reality of the table anymore.

<=> I still have several cases where the link fails because there is just no data to link to in the database_table - some areas are just not present in that table. Still, they show up in my chart.

I guess that's because I also have some data in my Excel file that I show in the chart, so there is always something to show. I guess I'll have to build that as a formula into my dimension_fields - but how to do that? I cannot query a non-existent value in another table for anything, not even for its existence, can I?

[marcus_sommer]

It will depend from your datamodel if it could be counted - the result of 0 or NULL from a query indicates that there aren't values available, often is a len(Field) already enough.

Otherwise it might be worth to consider if the (respectively this part of the) datamodel is appropriate to your aim and a concatenate from the datastructures and/or a consequent handling of NULL or missing data isn't more suitable, maybe like above mentioned.

- Marcus