Merge Date Dimensions

[#1]

Hi guys.

I have a table that stores employees informations. Two of those of these informations are Demission and Admission date.

I want to create e chart bar report that shows per month the count of demissions and admissions (turn over analysis) and I can't figure out how I can do that.

The first thing I though was to create a load script like the one I post below. But I think it's not a good solution, since it would be a useless table and I would use it only for a chart.

Is there a way to merge those two fields into one and flag them as "admission" ou "demission" just for a chart bar report? Maybe with AGGR function?

I've searched a lot in forum but I couldn't get a answer.

/*Example Script*/

[UselessTable]:

LOAD

    DemissionField as Date,

    'Demission' as Type

    FROM [lib://RH/Dim_Employee.qvd]

(qvd);

LOAD

    AdmissionField as Date,

    'Admission' as Type

    FROM [lib://RH/Dim_Employee.qvd]

(qvd);

Thanks all in advance

[ogster1974]

No problem Janilson

Your not duplicating your data you are just separating your facts.

Fact 1 When the Employee was admitted

Fact 2 When the Employee was demitted

Qliks associative data model loves working with data in this way.  You just need to get comfortable with set analysis and some other techniques to extract the data from this structure and display it in a way your user community wants to see it in.

Have a look into Star and Snowflake schemas to see why Qlik ideally wants the data structure in this way.

Regards

Andy

[JonnyPoole]

Hey guys ! i agree 100% with Andy. 

Also i wanted to offer up this technical explanation of why more rows can result in a smaller in-memory footprint.

If you read the following presentation you will see that replacing 2 dates with 1 date will eliminate a symbol table from the model and more than likely result in fewer rows in that same symbol table than the combined rows from the 2 earlier tables. This is because Qlik cares heavily about data uniqueness, and if you use one list to store all the possible dates rather than two, you could reduce (up to half) your storage requirement.  The new symbol table for type will literally only have 2 possible values = 2 rows, not matter how many rows are in there.

True , the data table does get longer but if there are not too many columns, the bit stuffed pointers that replace the symbol values will not require a very heavy burden.

WHen you add everything up, extra rows of data can increase the storage, but eliminating uniqueness by consolidating fields and reducing unique values will have the opposite effect, creating a dampening effect of adding more rows .   Its a great scalability model.

The data scalability of Qlik's in-memory associative data model

[#4]

So there's no problem. I didn't know that Qlik only store unique values.

Thanks (again) all for help and for technical explanation.

It's all clear to me now.

P.S.: Of course I will save this .ppt file, it's very good. Congratulations