topic Table Creation Involving Calaculated Dimensions in QlikView

Table Creation Involving Calaculated Dimensions

Tue, 19 Jan 2010 11:46:09 GMT

Hi everyone,

We have a problem in which we have some null values for the field 'Wave'. what we would like to do is detailed below. If there is no ParentCatalogueId we would like the 'Wave' status and 'Score' to remain. However, for CatalogueId 3 where a ParentCatalogueId exists (which never has a 'Wave' status i.e. is always Null), we would like to use CatalogueId 1 instead of 3 and also the corresponding Wave Status.

We can see that the table would work in the same way for CatalogueId 4. A parent CatalogueId exists, (which is 2) so it uses CatalogueId 2, the Wavae Status which is 2 and the score which is 100.

CatalogueId	ParentCatalogueId	Wave	Score	InheritedWave	InheritedScore
1		1	100
2		2	100
3	1		10	1	100
4	2		35	2	100

This is the table we are trying to produce but I'm not sure what way to create the calculated dimensions which would be InheritedWave and InheritedScore. I know there would be some if statements involved but my attempts so far have been unsuccessful.

If anyone has any ideas it would be much appreciated.

Regards,

Table Creation Involving Calaculated Dimensions

spsrk_84 — Tue, 19 Jan 2010 12:16:13 GMT

Hi,

I got the solution for ur query ,i am attaching the qvw file please find the attached and gothrough the script wriiten u will understand the logic.

Cheers,

Ajay

Table Creation Involving Calaculated Dimensions

Tue, 19 Jan 2010 17:19:26 GMT

Hi Ajay,

Thanks for your reply. This looks as though this could be the solution. However, some of our fields come from different tables. Would you suggest joining the tables beforehand? Wava and CatalogueId (along with a number of other fields) reside in one table and ParentCatalogueId and Score reside in another.

Table Creation Involving Calaculated Dimensions

spsrk_84 — Wed, 20 Jan 2010 12:33:34 GMT

Hi,

Can u please send me the table structure so that i can verify whether joining the tables results in exactly what u required

Regards,

ajay

Table Creation Involving Calaculated Dimensions

Wed, 20 Jan 2010 12:58:36 GMT

Hi Ajay,

We have three tables (first is a mapping table and other 2 are the data sources) :

Response_Mapping:
Mapping Load Distinct (CatalogueID) as CatalogueId,
 ResponseText;
 select
 CatalogueID,
 ResponseText
 from
 dbo.View;
REPTEC_QUESTIONNAIREFACT:
load Distinct (CatalogueID) as CatalogueId,
 ParentCatalogueID,
 ResponseText,
 If(ISNULL(CopiedFromCatalogueID)=-1,ResponseText,ApplyMap('Response_Mapping',CopiedFromCatalogueID,ResponseText)) as InheritedResponseText;
select
 CatalogueID,
 ParentCatalogueID,
 ResponseText,
FROM
dbo.Questionnaire
where ResponseText in ('Compliant','Not Compliant', 'Not Applicable', '');

LtOTable:
LOAD CatalogueID as CatalogueId,
 SystemName as LtOApplication,
 Wave,
FROM
[$(vServerName).xls] (biff, embedded labels, table is [LtO Data$]);

The score we mentioned previously is a long expression so we chose to map to responsetext instead which we have done. However to create an inheritedwave field is proving more difficult. We want to say, if there is a ParentCatalogueId use this as the CatalogueID and it's corresponding Wave status if it exists.