Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mkubbutat
New Contributor II

Generate new Dimension from different columns

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

6 Replies
fvelascog72
Valued Contributor

Re: Generate new Dimension from different columns

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

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;

Life is so rich, and we need to respect to the life !!!
effinty2112
Honored Contributor

Re: Generate new Dimension from different columns

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

mkubbutat
New Contributor II

Re: Generate new Dimension from different columns

H Anil,

your suggestion worked perfectly well. Really great help.

Thank you very much!

Michael

mkubbutat
New Contributor II

Re: Generate new Dimension from different columns

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

Screenshot.JPG

mkubbutat
New Contributor II

Re: Generate new Dimension from different columns

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

Community Browser