Join SELECTs from two tables

[datanibbler]

Hi,

I have a rather complex scenario:

- I query data from one database table where there is only one record per month - so there is no date_field, only a year_field and a month_field.

- To be able to link the table to my master calendar and select on weeks or months there, I have thus created one with Makedate.

=> This date is always the 1st of a month.

- Now I am querying data from another table where there is one record per day, so there is a date_field here.

- I query that second one only for days of the current month.

=> The idea now is to join those two tables and store them as a qvd file, then load that qvd file into my QlikView app and display a diagram based on it.

The problem I cannot currently overcome is this: The date I have created in the first table, as I said, is the 1st of every month, so when I select July 4, I get no value from that table. I have tried to use a set expression fixing the date_field on the start of the current month - that does not currently work.

My code is

sum({1<%Datum = {$(=Monthstart(TODAY()))}>}ÜS)

I have tried several times, it should actually work that way, no? Well, it doesn't.

Can anybody tell me what might be the reason this doesn't work and how I could pinpoint that?

Thanks  alot!

Best regards,

DataNibbler

[datanibbler]

Hik,

I've made it now. This is really challenging in a way - one has to do a lot of experimenting with single and double quotes...

Finally, my code is just a very little bit different:

= sum({<%Datum = , Regdatum = {'$(=Monthstart(TODAY()))'}>}ÜS)

This requires two separate date_fields... That way, the user can do selections on the one and I can still get the value from the other table where I know that >Monthstart(TODAY())< will return the value as it was on the last working day.

I would like to make it with just one date_field so I can more easily join the two for storage as a qvd.

Can I do that? Write a set expression that will ignore the user_selection on a date_field and set it to another value instead?

Thanks a lot!

Best regards,

DataNibbler

[Anonymous]

This is something I would like a more satisfactory way of dealing with also. It arises for me where a budget is set at the Year-Month level while transactions are at the Year-Month-Day level.

The first design issue for me is that the master calendar is linked into my data models at the Year-Month-Day level, and can't be linked at the Year-Month level as well without having some sort of many-to-many link in between. This might be a potential way to go, but I'm worried about double counting.

The second design issue is that monthly amounts can't be set up per day, which would allow for the monthly data to be associated with each daily entry. I have considered dividing the monthly amounts up into daily amounts, but think rounding would be an issue.

The way I have gotten around this so far is to do a group by on the daily transactions, find which day of each month has the most daily transactions, and use these dates to link the monthly amounts into the datamodel (as the first each month may not have transactions, and the monthly amounts might not therefore be associated). As this doesn't support drilldown into the data I end up having to hide the budgeted monthly amounts when selections of the transactions have been made.

It's pretty messy and I've love to have a better solution. Sorry for piggy backing on your question Friedrich.

Jonathan

[datanibbler]

Hi jonbroughavone,

you're welcome.

It would be handy to be able to construct like a "generic date", wherein the month and year would be defined, but the day would be free.

=> That way, I could enter any day within a month and get the value from my monthly_table. Currently, I have constructed a date there - always the 1st - so I cannot enter the 2nd, I will get no value.

Well, I will go on trying. In principle, it should not be hard:

- Extract from both tables

- Concatenate (in a RESIDENT LOAD)

- Sort the entire table

- Use the PEEK function to sum up the daily values.

Sorry, jonbroughavone, I don't quite understand your problem, but

- I also have a master calendar and when I have a date (year-month-day) to link a table to it, I can also make selections on the year, month or week fields which are part of the master calendar. No need for a second link. When, on the other hand, I have no day (just a month and year), like you have, that is a limitation - I can construct a date, as I have done, but only one, and the calendar is linked by only that date - just one day per month.

- The monthly amounts, that's pretty similar to the problem I am facing. You might be able, in a RESIDENT LOAD, to construct a new table with one record per day and, using the PEEK function, just copy the monthly amount to every day within that month?

I will not blow up my table like that because it would be sufficient to have daily values in the current month and monthly values before that.

I have just realized that when I just extract from the daily_table without any time_restrictions, I seem to get pretty accurate values. I will just backcheck if that's okay and if so, I will skip that whole thing about concatenating from two tables. QlikView loads just 25sec on my PC which is okay.

Best regards,

DataNibbler