Compacting a table so I can join it into my data_model?

[datanibbler]

Hi,

I have a table I would like to include into my data_model just for one field - but the field which I can use as joinfield is not unique. There is a date component, so I have to select the most current (biggest date-time-stamp) value per keyfield. I need the field PACKING_PLAN_DETAIL.QUANTITY and the datefield I have to use is BUSINESS_PLAN.CREATION_DATE.

can somebody help me with the SET_analysis syntax I need to do that?

I have just read up in "QlikView 11 for Developers" and it should be like that - well, obviously not quite for it doesn't work:

LOAD

...

{$<BUSINESS_PLAN.CREATION_DATE = {$(=Max(BUSINESS_PLAN.CREATION_DATE))}>} PACKING_PLAN_DETAIL.QUANTITY

FROM

...

Thanks a lot!

Best regards,

DataNibbler

[er_mohit]

set Analysis works in expression not in script

[datanibbler]

Hi er.mohit,

there seems to be something wrong still: Examining that field in the table_viewer_window tells me it is populated in every record - but when I put it into my diagram (the formula), I don't get a value at all.

Best regards,

DataNibbler

[gandalfgray]

Try this technique which you can use in your load script:

(you obviously need to adapt it to your tables)

// create sample data:
costTable:
LOAD * INLINE [
    item, cost_date, cost
    A, 2012-01-01, 12
    A, 2013-01-01, 15
    B, 2011-06-14, 13
    B, 2012-09-10, 10
    B, 2013-03-01, 11
];
// ------------------------------
// Find the max data value for eacj item and create a combined field
_maxCostDatePerItem:
LOAD item&'@'&Date(max(cost_date)) As items_max_cost_date
Resident costTable
Group By item;
// Get Data for the row that matches the combined field (that is what the Exists() function  do)
currentCosts:
LOAD item,
    cost,
    cost_date As cost_effective_since_date
Resident costTable
Where Exists(items_max_cost_date,item&'@'&cost_date);
DROP Tables _maxCostDatePerItem, costTable; // Clean up any tables you dont need anymore (optional)

[Gysbert_Wassenaar]

// ---- create sample data:

costTable:

LOAD

    item

    ,date#(cost_date,'YYYY-MM-DD') as cost_date

    ,cost

INLINE [

    item, cost_date, cost

    A, 2012-01-01, 12

    A, 2013-01-01, 15

    B, 2011-06-14, 13

    B, 2012-09-10, 10

    B, 2013-03-01, 11

];

// ---- right join with maximum cost_date values

right join load item, max(cost_date) as cost_date

resident costTable group by item;

// ---- done

[datanibbler]

Hi Gysbert,

your idea seemed very good - simple, and it would give me a new table where the keyfield ITEM_Nr should be unique and which I could thus join to another table to keep my data_model compact.

Unfortunately, however, it does not work:; After writing this new code (adapted of course) and reloading the script, that table has the same nr. of records it had before and my keyfield is still not unique...

There is probably only one bit missing - the script_progress dialog shows me that there are actually two LOADs for that table, the forst with approx. 20k lines, the second with just 3k lines - but after the reload has finished, I can see that the table still has those 20k lines...

What is missing to complete that operation? I cannot DROP the table so easily since there is no new table...

Thanks a lot!

Best regards,

DataNibbler

Ah -. looking closely at your code confirms what our external consultant just told me: All fields in that table must be either the ones to aggregate or they must be in the GROUP BY clause, right?

That is another particular that's not mentioned in the book.

Is there any feedbyck possibility? there should be...

[datanibbler]

Hi,

I made it now - almost.

I had to adapt my code to that general SQL principle, so I used just three fields and included two of them in the GROUP BY clause.

I did that in a RESIDENT load and dropped the original table afterward.

Now my new table has just a few thousand records instead of 20k.

However, the keyfield is still not unique: For some reason, there are still two records for certain item_numbers - and when I build that new field into my diagram, I don't get any value.

Best regards,

DataNibbler