topic Re: Generate new Dimension from different columns in QlikView

Generate new Dimension from different columns

Anonymous — Mon, 16 Oct 2017 07:38:08 GMT

Good morning,

I am stucked with the following task:

In a table I have 5 columns:

a) Project ID

b) Invoice Date 1

c) Invoice Value 1

d) Invoice Date 2

e) Invoice Value 2

I would like get a bar chart, depicting

dimension: all invoice dates from columns b and d

formula: all invoice values from colomns c and e

I thought to generate two new columns "Invoice Date All" and "Invoice Value All" while loading the table.

Any idea how to put this in the script?

Any help is highly appreciated.

Michael

Re: Generate new Dimension from different columns

fvelascog72 — Mon, 16 Oct 2017 09:08:27 GMT

Hi,

Have you think in concatenating the values to create a table with:

a) Project ID

b) Invoice Date

c) Invoice Value

Saludos.

Re: Generate new Dimension from different columns

Anil_Babu_Samineni — Mon, 16 Oct 2017 09:11:56 GMT

May be this?

Load [Project ID], [Invoice Date 1] as [Invoice Date All], 'Date 1' as Flag, [Invoice Value 1] as [Invoice Value All] From Table;

Join // Inner Join

Load [Project ID], [Invoice Date 2] as [Invoice Date All], 'Date 2' as Flag, [Invoice Value 2] as [Invoice Value All] From Table;

Re: Generate new Dimension from different columns

effinty2112 — Mon, 16 Oct 2017 09:58:31 GMT

HI Michael,

I think Federico's idea is worth trying. Something like:

Table:

Load

[Project ID]

[Invoice Date 1] as [Invoice Date]

[Invoice Value 1] as [Invoice Value]

From ...

Load

[Project ID]

[Invoice Date 2] as [Invoice Date]

[Invoice Value 2] as [Invoice Value]

From ...

Regards

Andrew

Re: Generate new Dimension from different columns

Anonymous — Mon, 16 Oct 2017 11:24:29 GMT

H Anil,

your suggestion worked perfectly well. Really great help.

Thank you very much!

Michael

Re: Generate new Dimension from different columns

Anonymous — Mon, 16 Oct 2017 11:43:12 GMT

Hi Frederico,

Thank you for your feedback.

I tried your suggestion, but it did not work properly if I had to use more that two columns (Invoice Date 1; Invoice Date 2 ... Invoice Date 4)

It allows loading the values even with 4 Inovice dates correctly (checked in a table box).

But if I set up a bar chart in which I use Month([Invoice Date]) as a dimension, and sum([Invoice Value]), in each month that has more than two values the sums are not calculated properly (see month June, July, Oct.)

Only months in which I do have 1 or 2 entries are shown correct values. (Please see screen shot).

The suggestion by Ani to use "inner join" worked in this setting very well.

Kind regards

Michael

Re: Generate new Dimension from different columns

Anonymous — Mon, 16 Oct 2017 14:16:49 GMT

Hi Frederico, Hi Andrew,

I recheck my script, and I figure out why the bar chart did not show the correct values.

Rather stupid mistake.

I can now say that your suggestion to concatenating the data in a table works also perfectly well.

Sorry for any confusion.

And many thanks to you for helping out.

Michael