topic Re: Compacting a table so I can join it into my data_model? in QlikView

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

datanibbler — Fri, 10 May 2013 09:27:04 GMT

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

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

er_mohit — Fri, 10 May 2013 09:29:48 GMT

set Analysis works in expression not in script

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

datanibbler — Fri, 10 May 2013 10:16:06 GMT

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

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

gandalfgray — Fri, 10 May 2013 11:28:26 GMT

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)

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

Gysbert_Wassenaar — Fri, 10 May 2013 11:37:30 GMT

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

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

datanibbler — Fri, 10 May 2013 13:27:30 GMT

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

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

datanibbler — Fri, 10 May 2013 14:29:21 GMT

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