Can't get rid of synthetic joins and table

[#1]

Please forgive my ignorance - I'm still very new to QlikView, and really struggling to get to grips with it.  For reasons best known to themselves, our management have given us until end of this year to get a suite of dashboards ready even though we've not yet had Developer I & II training...  We've had a couple of very helpful consultancy days from the QlikView trainer who delivered Designer I & II, but I'm still not quite up to speed.

The background:

With the help of the trainer, we have a "QVD generator" document that loads data from a number of different DB views.  We then have a 'master' or 'template' QVW file which loads data from all the QVD files, and which our end users will use to create their own dashboards.

The problem:

I'm trying to add more QVDs into the 'master' dashboard, but having trouble linking the data - I'm either getting loops or sythentic joins.  Specifically I have a QVD file called "pl_slam.qvd" and one called "calendar.qvd" which I'm loading as tables called pl_slam and calendar respectively which are populated in their own tabs within my QVW.  I also have a "link" tab in my QVW where I'll do any linking as required.

pl_slam has a column called "month_no"

calendar has a column called "fy_month_number"

These columns contain the same data, so I want to join these.

These are the things I've tried so far without success:

1.  Rename month_no as fy_month_number in pl_slam (because that's what it's called in calendar), then doing a concatenate load fy_month_number from resident pl_slam;

2.  Add a new hidden field called %fy_month_number in pl_slam, then do a concatenate load on %fy_month_number in my link tab, then dropping month_no from pl_slam;

3.  Same as 2 (above) but also adding a %fy_month_number into calendar (but keeping the original/unhidden fy_month_number in calendar).

I've tried various combinations of the above, but so far still can't get rid of the $syn warnings and in each case I'm not getting month_no data from pl_slam int the appropriate place in my dashboards.

The goal:

I want a straight table to have "month", "actuals", and "planned".  At the moment I can't get "planned" data to separate out - I get my actuals per month, but the planned column is empt (except for one row, with no month, which is a sum of all planned data.  All my planned data is being aggregated and not assigned to a month.

Sorry for such a lengthy first post!  I hope it makes some sort of sense...  If anyone can understand what I'm babbling about, and if anyone can help me out, I'd be most grateful!

Thanks,

Dan.

[swuehl]

Dan,

how do you rename your field month_no? Using the rename statement? I think this will just silently fail, since the target field name already exists (not documented in the help).

Or have you tried using AS, like

LOAD

....

month_no as fy_month_number

...

from pl_slam.qvd;

I think this should work, but if you have tried this, could you post the script code you are using?

Regards,

Stefan

[#3]

Hi Stefan,

I'm using the "AS" operator to rename.  In my current this attempt, I can see aggregated plan data and aggregated actuals - but now I don't see a month name 😕

Here's a snippet of code from my current attempt:

pl_slam:

LOAD

...

  month_no,

...

FROM

(qvd);

calendar:

LOAD fk_date as %date,

  fy_month_number as %fy_month_number,

...

FROM

(qvd);

And here's a snippet from my link tab:

Concatenate load

month_no as %fy_month_number

resident pl_slam;

And finally here's a snippet from my 'cleanup' tab:

drop field month_no from pl_slam;

End result is now this:

Previously at least I had month_name and actuals, but plan was all zero (except the sum of ALL plan, which was listed against a blank/null month).

[swuehl]

If this is literally what you are using

Concatenate load

month_no as %fy_month_number

resident pl_slam;

then you concatenate some month values to calendar table, but you are not linking those two tables.

Try this:

- remove or comment out

Concatenate load

month_no as %fy_month_number

resident pl_slam;

Modify your first load:

pl_slam:

LOAD

...

month_no as %fy_month_number,

...

FROM

(qvd);

I assume you have matching month values in both your calendar and pl_slam.

Then let's see if we make progress.

Regards,

Stefan

[#5]

The outcome is the same as before, unfortunately.  I'm really sorry - I neglected to mention another join on the pl_slam table (I didn't think it was important, but it clearly is!)  I now have a loop in my table structure:

I've followed your suggestions above, but in addition I'd also joined pl_slam to my link table.  Here's the full script for the pl_slam table:

pl_slam:

LOAD

    spell_attendance_id as %pl_slam_link,

//   consultant_code,

//   consultant_name,

     month_no as %fy_month_number,

     pct_code,

     pct_name,

     ApplyMap('pct_grouped',pct_code,'UNKN') as pct_group,

     spec_group_code,

//   spec_code,

//   spec_name,

//   hrg_code,

//   hrg_name,

     pod_code,

     pod_name,

     ApplyMap('pod_grouped',pod_code,'UNKN') as pod_group,

     patid,

     spell_attendance_id,

     nhs_no,

     list_code,

     spell_attendance_end_date,

     gp_practice_code,

     gp_code,

     activity_plan,

     activity_actual,

     activity_variance,

     price_plan,

     price_actual,

     price_variance

//   fy_year

FROM

(qvd);

Thank you for your help though - very much appreciated!  And sorry not to have included all relevant info the first time (let me know if I've missed out any other relevant info).

Dan.

[swuehl]

Maybe we are talking too much about details while we should talk about what you want to achieve on a bigger level.

I don't understand your link table yet, what do you want to achieve with that table?

As I see, both price_actual and price_plan / activity_plan and activity_actual are originated in pl_slam, these are the values you are after?

Your pl_slam data seems to be month_no base, but wait -  you've said:

>>At the moment I can't get "planned" data to separate out - I get my actuals per month, but the planned column is empt (except for one row, with no month, which is a sum of all planned data.  All my planned data is being aggregated and not assigned to a month. <<

So maybe we have to look also how the data is organized in your source table / qvd, I am not sure.

Hm, that's  a lot unknown territory for me. Would it be feasible that you upload a small sample file with dummy data (I guess probably not..).

If you just started with QV, have you already looked into the tutorial? This at least covers some basics also with scripting I believe. Maybe there's also a free designer 1 e-learning, need to check this, too.

If you want me to help, I would suggest that you start with some more words about the layout of your source tables (the relevant fields) and what your required outcome is (I know, the table, but please describe how the UI table relates to your data model, e.g. what are the actual and plan fields / expressions you want to show).

Regards,

Stefan