Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody! I'm a total newbie in Qlik and trying to make my first steps. I have a denormalized table (xlsx) containing the following (in real there much more fields,but these are the most meaningfull):
CompanyID | CompanyName | EndOfService | ContractID | ContractDate |
---|---|---|---|---|
1 | ABC | 01.12.2013 | 1 | 01.01.2005 |
1 | ABC | 01.12.2013 | 2 | 01.01.2007 |
2 | ZZZ | 3 | ||
3 | ThirdOne | 4 | 01.01.2001 | |
3 | ThirdOne | 5 | 01.01.2010 | |
1 | ABC | 01.12.2013 | 7 | |
3 | ThirdOne | 8 |
In the data load script I try to normalize it splitting into 3 tables - Companies, EOS and Contracts:
Comps:
Load
CompanyID,
CompanyName,
EndOfService
From src.xlsx (...);
EOS:
Load
CompanyID,
date(min(ContractDate)) as StartOfService
From src (...)
group by CompanyID;
Contracts:
Load
CompanyID,
ContractID,
ContractDate
From src (...)
The earliest date of contract for a company is the Start of Service date. According to the table, I have 3 types of companies: 1. "Old" - we had contracts with it, but they came to an end (StartOfService not null, EndOfService not null. Example is CompanyID=1) 2. "Project" - we have the company in our DB, but have never had a contract with it (SOS null, EOS null. Example is CID=2) 3. "Active" - we have at least one active contract with it (SOS not null, EOS null. Example is CID=3). I need to put some kind of 3-way switch on the sheet to filter the data.
What way(s) of implementing it do I have? Build another table with field of 'status' based on nested IF and using a join of 3 tables as a data source? Or there is an easier way? Thanks in advance!
Solved for me.
TempOrgs:
LOAD * FROM src(...);
OrgDta:
Mapping LOAD CID,
date(min([ContractDate]))as SOS
resident TempOrgs
group by CID;
Comps:
LOAD
...,
ApplyMap('OrgDta',CID,Null()) as SOS,
if(IsNull(ApplyMap('OrgDta',CID,Null())),'Project',if(IsNull([EndOfService]),'Active','Old')) as Status
Resident TempOrgs;
Contracts:
LOAD ... resident TempOrgs;
Drop Table TempOrgs;
Thanks for all who helped me find the solution.
Hi
Its not strictly necessary (and sometimes even undesirable) to normalise the Qlikview model, specially if its coming from a denormalised source like Excel.
Why don't you try just loading the Excel data with minimal transformation at first. During the load, you can classify the companies using the rules you outlined in your post, and add that field to the data table. Also add a master calendar(s) and link one to each date field, or use a link table - search master calendar on this site for lots of discussion on this topic.I have done on model that way that worked well.
I suggest that you read the Qlikview design blog by Henric Cronstom - also on this site. There are lots of tips in there on model design and Qlikview techniques.
BTW- there is nothing wrong with the code you posted. If the minimal transformation doesn't work, this could be the next step.
HTH
Jonathan
Thanks for you reply. I don't quite catch, how can I perform the aggregations I need (SOS) and calculations based on the aggregated fields (status) at the same load step. In fact src.xlsx has about 30 columns, 25 of them contain data of the companies, 5 of them - contracts. Any use of group by will either cut the contracts details or give me wrong results in defining SOS. AFAIR, aggr function is not available at the load script. Can you provide me with a simple example of loading, aggregating and defining (status classification) at a single load operation? Based on the src table above. Thanks!
Alex
Please provide the field and table structure .
If possible then the sample.
There's no need to place the original src table here because it contains personal data that must not be published and all the data in it is in russian locale so it would be hard to understand. The dummy table I placed in the first post of the topic is quite suitable for the sample needs. I think, I wouldn't face a problem extrapolating it to my working environment.
As the result I expect to see something like that (dst sheet):
Maybe I misled you little bit. There can be draft contracts with all companies. They have NO date of contract (ContractDate null) so this field can't be a criterion to set status without aggregation. I've added a couple of lines to the table in the first post.
Solved for me.
TempOrgs:
LOAD * FROM src(...);
OrgDta:
Mapping LOAD CID,
date(min([ContractDate]))as SOS
resident TempOrgs
group by CID;
Comps:
LOAD
...,
ApplyMap('OrgDta',CID,Null()) as SOS,
if(IsNull(ApplyMap('OrgDta',CID,Null())),'Project',if(IsNull([EndOfService]),'Active','Old')) as Status
Resident TempOrgs;
Contracts:
LOAD ... resident TempOrgs;
Drop Table TempOrgs;
Thanks for all who helped me find the solution.