topic Nested selection in formula in QlikView

Nested selection in formula

Fri, 20 Jul 2012 13:46:02 GMT

Hi folks,

this discussions were always helpful but unfortunately I encountert a problem that I couldn't solve myself nor by checking here.

I need a formula which shows me a entity in a dimenson of a diagramm which hast a [dateto] which is one day less than the [datefrom] from the entity with the ultimo [dateto] (as implemented in my database as '31.12.9999'). This is a Pseudo-Code to show what I want, not my approach in the formula editor, which I rather ingore because I suppose it's rather confusing.

[entity] WHERE
[dateto] EQUALS

[datefrom] -1 WHERE

[dateto] EQUALS

'31.12.9999'

I'd like to thank everybody who's looking at my problem in advance and maybe someone can help me.

Thanks

Markus

Re: Nested selection in formula

brenner_martina — Fri, 20 Jul 2012 15:59:06 GMT

Hi, Markus,

I think your formula is not possible, because you say dateto = datefrom-1 AND dateto='31.12.9999'? You can in one field only get one equalance!

Greetings from Munich

Martina

Re: Nested selection in formula

Fri, 20 Jul 2012 17:51:46 GMT

Can I store a subset then?

Or in another approach I rather show where I'm going with all this (and my approach is nonsense or not elegant). My data looks something like this:

[ID]; [DATEFROM]; [DATETO]; [FACTOR]

1; 01.01.2000; 12.01.2006; 1,234

1; 13.01.2006; 10.04.2008; 2,050

1; 11.04.2008 07.07.2012; 2,345

1;     08.07.2012;     31.12.9999;     1,456
2;     03.05.2006;     18.03.2010;     23,45
2;     19.03.2010;     20.12.2011;     25,00
2;     21.12.2011;     31.12.9999;     21,22

3; 16.10.2011; 31.12.9999; 5,111

3; 05.01.2007; 15.10.2011; 4,999

So I need to compare the last current two [factor] to each other, without knowing how much entries each [ID] has and in which order they apper. Also the date of the single previous entity isn't know but can be calculated by [datefrom]-1 of the [dateto] = 31.12.9999 entity.

Re: Nested selection in formula

brenner_martina — Mon, 23 Jul 2012 08:36:36 GMT

Hi, Markus

What about this in script?

Left Join (Data)

LOAD ID,

Max(DATETO) As MaxDate

Where DATETO <> '31.12.9999'

Group By ID;

Than you can work in your expression like this:

If(DATETO = MaxDate, FACTOR)

Greetings from Munich

Martina