topic Re: ?Complicated? logical problem - plz help in QlikView

?Complicated? logical problem - plz help

datanibbler — Fri, 22 Jan 2016 15:07:46 GMT

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.

Re: ?Complicated? logical problem - plz help

MK_QSL — Fri, 22 Jan 2016 15:13:33 GMT

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.

Re: ?Complicated? logical problem - plz help

datanibbler — Fri, 22 Jan 2016 15:18:27 GMT

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.

Re: ?Complicated? logical problem - plz help

MK_QSL — Fri, 22 Jan 2016 15:24:36 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 10:11:19 GMT

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

Re: ?Complicated? logical problem - plz help

MK_QSL — Mon, 25 Jan 2016 10:15:00 GMT

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

Re: ?Complicated? logical problem - plz help

marcus_sommer — Mon, 25 Jan 2016 10:15:14 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 10:18:40 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 10:37:09 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 10:44:14 GMT

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?

Re: ?Complicated? logical problem - plz help

marcus_sommer — Mon, 25 Jan 2016 10:48:39 GMT

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.

Re: ?Complicated? logical problem - plz help

Mon, 25 Jan 2016 11:01:11 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 11:04:23 GMT

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

Re: ?Complicated? logical problem - plz help

marcus_sommer — Mon, 25 Jan 2016 11:08:44 GMT

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.

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 11:13:05 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 13:29:40 GMT

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.

Re: ?Complicated? logical problem - plz help

marcus_sommer — Mon, 25 Jan 2016 13:53:52 GMT

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

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 14:03:41 GMT

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.

Re: ?Complicated? logical problem - plz help

datanibbler — Mon, 25 Jan 2016 14:22:26 GMT

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?

Re: ?Complicated? logical problem - plz help

marcus_sommer — Mon, 25 Jan 2016 14:36:20 GMT

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