Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

A switch based on values of 3 tables to put on sheet

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):

CompanyIDCompanyNameEndOfServiceContractIDContractDate
1ABC01.12.2013101.01.2005
1ABC01.12.2013201.01.2007
2ZZZ3
3ThirdOne401.01.2001
3ThirdOne501.01.2010
1ABC01.12.20137
3ThirdOne8

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!

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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!

sujeetsingh
Master III
Master III

Alex

Please provide the field and table structure .

If possible then the sample.

Not applicable
Author

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.

Not applicable
Author

As the result I expect to see something like that (dst sheet):

Not applicable
Author

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.

Not applicable
Author

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.