topic Re: Dimension logic in QlikView

Dimension logic

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Delow is the dimension logic used in the straight table.But here i need to sort the month also as hardcode to Aug in the same expression.

Kindly let me know if it is possible.

Logic : =if([Sold Location] = '000', 'Goal', if([Sold Location] = '999', 'Company', if([Sold Location] = '888', 'District', if(match([Sold Location],'17','18','29','70','76','102','105','129'),[Sold Location]))))

In the above logic i need to include month as well.

Thanks..

Re: Dimension logic

sunny_talwar — Mon, 03 Oct 2016 18:18:49 GMT

Where are you using month in this calculated dimension? I am not seeing that above?

Re: Dimension logic

nareshthavidishetty — Mon, 03 Oct 2016 18:28:10 GMT

Hi Sunny,

The requirement is to include the month filter in the above calculated dimension .

Thanks..

Re: Dimension logic

vishsaggi — Mon, 03 Oct 2016 18:40:45 GMT

I am not sure if this works, may be Sunny can come up with better way, mean while you can try this?

= IF( Month= 'Aug', Pick(Match([Sold Location], '000', '999', '888'), 'Goal', 'Company', 'District'),

IF(Month = 'Aug' AND match([Sold Location],'17','18','29','70','76','102','105','129'), [Sold Location]))

Re: Dimension logic

nareshthavidishetty — Mon, 03 Oct 2016 18:42:18 GMT

Hi,

It means my calculated dimension needs to include month as well.

Thanks..

Re: Dimension logic

Anil_Babu_Samineni — Mon, 03 Oct 2016 18:43:27 GMT

Hard code means this? Or else would you describe what is your intend to use this

=if([Sold Location] = '000', 'Goal', if([Sold Location] = '999', 'Company', if([Sold Location] = '888', 'District', if(match([Sold Location] ,'17','18','29','70','76','102','105','129'),[Sold Location], if(month(field) = 'Aug', 'Aug')))))

Re: Dimension logic

nareshthavidishetty — Mon, 03 Oct 2016 19:10:56 GMT

Yes..i need to hard code like this..but the logic is not working.

It's not filter the data for the month Aug and also it inserts an new column Aug in the table.

Thanks..

Re: Dimension logic

sunny_talwar — Mon, 03 Oct 2016 19:14:59 GMT

May be this:

=If(Month = 'Aug',

If([Sold Location] = '000', 'Goal',

If([Sold Location] = '999', 'Company',

If([Sold Location] = '888', 'District',

If(Match([Sold Location],'17','18','29','70','76','102','105','129'), [Sold Location])))))

=If(Num(Month) = 8,

If([Sold Location] = '000', 'Goal',

If([Sold Location] = '999', 'Company',

If([Sold Location] = '888', 'District',

If(Match([Sold Location],'17','18','29','70','76','102','105','129'), [Sold Location])))))

Re: Dimension logic

Anonymous — Mon, 03 Oct 2016 19:18:12 GMT

Please post your sample qvw

Regards,

Gireesh.

Re: Dimension logic

nareshthavidishetty — Mon, 03 Oct 2016 19:29:04 GMT

Hi Sunny,

The locations were came out as null.

Thanks..

Re: Dimension logic

sunny_talwar — Mon, 03 Oct 2016 19:30:14 GMT

Did you try both out? and non of them worked?

Re: Dimension logic

MarcoWedel — Mon, 03 Oct 2016 21:58:48 GMT

Hi,

instead of using a calculated dimension, another solution might be:

tabSampleData:
LOAD RecNo() as ID,
    Month(Date#(Ceil(Rand()*12),'M')) as Month,
    If(Rand()>0.7,Pick(Ceil(Rand()*11),'000','999','888','17','18','29','70','76','102','105','129'),Ceil(Rand()*10)) as [Sold Location],
    Ceil(Rand()*500) as fact
AutoGenerate 100;

mapSoldLocation:
Mapping LOAD * INLINE [
    SoldLocation, SoldLocationAlias
    000, Goal
    999, Company
    888, District
    17, 17
    18, 18
    29, 29
    70, 70
    76, 76
    102, 102
    105, 105
    129, 129
];

Left Join (tabSampleData)
LOAD Distinct
    [Sold Location],
    ApplyMap('mapSoldLocation',[Sold Location],Null()) as [Sold Location Alias]
Resident tabSampleData;

hope this helps

regards

Marco