?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.

[MK_QSL]

Create another field say CommonKT

This field would be as below

Considering KT1 to KT75 field name is KTField1

Considering KT101 to KT175 field name is KTField2

You can use

KTField1,

KTField1 as CommonKT

KTField2,

'KT' & (KeepChar(KTField2,'0123456789')-100) as CommonKT

Hope this would help.

[datanibbler]

Hi Manish,

yeah. This sounds good. That's just another method, however, to do essentially what I had in mind, namely to assign one name to two areas - KT1 and KT101 would in that scenario be assigned one common name, no?

I guess that's the way to go.

Thanks a lot!

P.S.: I could do that with a mapping table like the attached one, no?

The syntax would be >> Applymap('whatever', Bin, Bin) << - so QlikView would map all those KT places where KT1 and KT101 would both be mapped against the same value KT1, and just return the original Bin_name for all others - right?

P.P.S.: Unfortunately I cannot test that Applymap() because right now, there are no bookings on the "other" places (like KT101 or KT110 etc.), but there may be at any point in the future. That's why I'm keen on knowing that my logic will work.

[MK_QSL]

Using CommonKT (another field) you can have the original Field name as well.

[datanibbler]

Hi,

in this context, I have quite a stupid problem:

- My table in the Excel file with all the KT areas has 300 records.

- Two of them are one physical unit, all through the list

=> it should be 150 records when I load it with a DISTINCT no?

     (all other fields are equal every time for the two records belonging together)

=> Up to that point, there are 69 records in the table, so it should be 219 afterwards.

<=> For some reason, I still get 300 records from the LOAD in the Debugger, which is too moch

<=> When I look in the table_viewer, there are only 206 records, which is too few ...

What could be the reason for this?

Thanks a lot!

Best regards,

DataNibbler

[MK_QSL]

Honestly speaking, I couldn't understand your above explanation (even though I know you are always raising questions with good description).

Better if you can create dummy records in excel file and try to explain it..

[marcus_sommer]

Hi DataNibbler,

extend these loadings with recno/rowno and check then within a tablebox how many records you have and which are missing or too much.

- Marcus

[datanibbler]

Hi Marcus,

that sounds like a good approach. I'll just have to do some little preparatory work first and make sure that only this one table is loaded and it's not concatenated to anything, so that I can examine it more closely.

Let's see ...

Best regards,

DataNibbler

[datanibbler]

Hi Marcus,

can it be that due to this rowNo(), the two records which are otherwise identical are not so anymore now? The Debugger still shows 300 records when it should be 150, same as before - but this time, the Table_Viewer also says 300 records.

Without the rowNo(), it was just 13 records too few - I'll check again now.

Hmmm, strange - without that RowNo(), the Debugger still says 300 records - but that table itself now has the correct number of 150 records.

I'll try again with the other records being loaded beforehand ...

[datanibbler]

Hi,

now it's back to what it was - never mind what the Debugger says, I still end up with 206 records in the table - which is 13 too few - there are 69 before, so it should be (69 + 150) 219 records.

(and there is one completely blank line on top - well, blank but for a 0 which I load as a fixed value in the last field, to make the nr. and name of fields match for concatenating) so the actual number (as from a COUNT on the GUI) is 205.

I cannot make head or tail of this right now. Can you?