topic Create a dimension in load script in QlikView

Create a dimension in load script

caccio88 — Wed, 25 Nov 2015 12:03:45 GMT

Hi everyone,

maybe is trivial issue but i'm wondering to create calculated dimension in my script.

I'm able to create calculated dimension with simple formulas like in my attachment but i don't know how to create dimension a little bit complex.

As you can see here i created the "key" dimension and now i can calculate the value of sales for the dimension...but if i want to create a dimension that is "sales per key" how should i do??

Clear enough?

Thank u all.

sunindia‌ what do u think about it?

Messaggio modificato da Filiberto Cacciari Hi guys, i've attached also my qvw cause i've a new little problem. Now I display the sum of KeySales but I want that when the value of sales is the same it will be sum once. Sample_Int: LOAD * INLINE [ Dim1, Dim2, Dim3, Sales A, B, C, 10 A, B, C, 10 A, B, C, 5 A, B, C, 5 C, B, A, 15 C, B, A, 15 C, B, A, 5 ]; Sum(ABC) = 15 and not 30 Sum(CBA) = 20 and not 35

Re: Create a dimension in load script

Mark_Little — Wed, 25 Nov 2015 12:23:16 GMT

KeySales:

Load

Key,

Sum(Sales) AS KeySales

Resident Sample_Int

Group by Key;

Mark

Re: Create a dimension in load script

sunny_talwar — Wed, 25 Nov 2015 12:40:08 GMT

Just a minor change. Resident should be from Sample, instead of Sample_Int

KeySales:

Load

Key,

Sum(Sales) AS KeySales

Resident Sample

Group by Key;

Re: Create a dimension in load script

effinty2112 — Wed, 25 Nov 2015 13:55:40 GMT

Hi Filiberto,

I wouldn't use the phrase Calculated Dimension for the Key you made in your script because after you load this the field 'Key' will exist in the data model as a dimension, it will not need to be calculated in a chart.

A good tip would be to separate the components of your key with an uncommon character that isn't used in any of the concatenated fields. So instead of the line

Dim1 & Dim2 & Dim3 as Key,

so 'dog' & 'cat' & 'mouse' = 'dogcatmouse' = 'dogc' & 'atm' & 'ouse'

Try

Dim1 & '|' & Dim2 & '|' & Dim3 as Key,

'dog' & '|' & 'cat' & '|' & 'mouse' = 'dog|cat|mouse' <> 'dogc' & '|' & 'atm' & '|' &'ouse'

Hope this is useful.

Re: Create a dimension in load script

caccio88 — Wed, 25 Nov 2015 14:39:38 GMT

Thank you all for your contribute. I've definitely solved my issue. Mark6505 sunindia‌ Effinty2112‌

Re: Create a dimension in load script

Mark_Little — Wed, 25 Nov 2015 15:36:52 GMT

Hi,

If it was helpful can you mark is so

Many Thanks

Re: Create a dimension in load script

caccio88 — Wed, 25 Nov 2015 17:18:01 GMT

Hi Guys,

I've a new issue as you can see.

Sample_Int:

LOAD * INLINE [

Dim1, Dim2, Dim3, Sales

A, B, C, 10

A, B, C, 5

C, B, A, 15

C, B, A, 5

];

Sum(ABC) = 15 and not 30

Sum(CBA) = 20 and not 35

sunindia‌ Mark6505‌ Effinty2112‌

Re: Create a dimension in load script

settu_periasamy — Wed, 25 Nov 2015 17:39:31 GMT

Hi,

May be use the DISTINCT

Sample_Int:

Load Distinct *,Dim1&Dim2&Dim3 as Key;

LOAD * INLINE [

Dim1, Dim2, Dim3, Sales

A, B, C, 10

A, B, C, 5

C, B, A, 15

C, B, A, 5

];

KeySales:

Load Key,

Sum(Sales) AS KeySales

Resident Sample_Int

Group by Key;

Re: Create a dimension in load script

sunny_talwar — Wed, 25 Nov 2015 17:52:25 GMT

You want it to show 15 and 20 or 30 and 35?

Re: Create a dimension in load script

caccio88 — Wed, 25 Nov 2015 17:56:03 GMT

15 and 20

Re: Create a dimension in load script

sunny_talwar — Wed, 25 Nov 2015 18:12:38 GMT

Settu's script is working:

Re: Create a dimension in load script

caccio88 — Wed, 25 Nov 2015 18:15:03 GMT

How about this:

Sample_Int:

	LOAD * INLINE [
	Dim1, Dim2, Dim3, Sales
	A, B, C, 10
	A, B, C, 10
	A, B, C, 5
	A, B, C, 5
	C, B, A, 15
	C, B, A, 15
	C, B, A, 5
	];
	Sample:
	load
	Dim1,
	Dim2,
	Dim3,
	Dim1&Dim2&Dim3 as Key,
	Sales
	Resident Sample_Int;

	KeySales:
	Load Key,
	Sum(distinct Sales) AS KeySales
	Resident Sample
	Group by Key;

Re: Create a dimension in load script

sunny_talwar — Wed, 25 Nov 2015 18:17:07 GMT

Alternatively, this should also work:

Sample_Int:

Load *,Dim1&Dim2&Dim3 as Key;

LOAD * INLINE [

Dim1, Dim2, Dim3, Sales

A, B, C, 10

A, B, C, 5

C, B, A, 15

C, B, A, 5

];

KeySales:

Load Key,

Sum(DISTINCT Sales) AS KeySales

Resident Sample_Int

Group by Key;

Re: Create a dimension in load script

caccio88 — Wed, 25 Nov 2015 18:21:57 GMT

Yep. Like what I've posted moment ago. thx all of you

Re: Create a dimension in load script

sunny_talwar — Wed, 25 Nov 2015 18:24:26 GMT

Ya, I think it should work (like I mentioned below)