topic Re: Houston, we have a problem in QlikView

Houston, we have a problem

Mon, 17 Nov 2014 10:16:09 GMT

Hi Gentlemen,

I have got an issue with below simple data:

imiona:

LOAD * INLINE [

name, sec_name, last_name

Gysbert, Miguel, Brown

Miguel, Ashfaq, Yellow

Ashfaq, Gysbert, Black

Gysbert, Miguel, Green

];

I would like to get info how many times name existed as first name and also as second name.

Correct result in pivot table would be as below:

Name | As first name | As second name

Gysbert | 2 | 1

Miguel | 1 | 2

Ashfaq | 1 | 1

Please help what expressions I should use.

Thank You in advance & Best Regards

Sebastian

Re: Houston, we have a problem

Mon, 17 Nov 2014 10:54:49 GMT

Oh, our top contributors became part of your launch mission.

This can't be done in pivot, as both first name and second name will be included in the dimensions and this will interfere with chart calculation. Its attainable in script.

I'd like to see a much better approach than this though and for now, I can only think of this.

Here:

imiona:

LOAD * INLINE [

    name, sec_name, last_name

    Gysbert, Miguel, Brown

    Miguel, Ashfaq, Yellow

    Ashfaq, Gysbert, Black

    Gysbert, Miguel, Green

];

NoConcatenate

IMIONA_2:

LOAD

name,

sec_name,

last_name

RESIDENT

imiona;

Left Join

LOAD

sec_name,

count(sec_name) as sec_name_count

resident

imiona

Group by sec_name;

Left join

Load

name,

count(name) as first_name_count

resident

imiona

Group by name;

Drop Table imiona;

Re: Houston, we have a problem

Mon, 17 Nov 2014 11:04:29 GMT

Thank You for Your quick reply.

It is ok. The result is correct. I am still wondering if it is possible to simply define it in expression using some magical function. Any ideas Guys?

Thank You in advance,

Sebastian

Re: Re: Houston, we have a problem

ashfaq_haseeb — Mon, 17 Nov 2014 12:40:22 GMT

Hi,

Slight modification

imiona:

LOAD * INLINE [

name, sec_name, last_name

Gysbert, Miguel, Brown

Miguel, Ashfaq, Yellow

Ashfaq, Gysbert, Black

Gysbert, Miguel, Green

];

NoConcatenate

IMIONA_2:

LOAD

name,

sec_name,

last_name

RESIDENT

imiona;

Left Join

LOAD

sec_name as name,

count(sec_name) as sec_name_count

resident

imiona

Group by sec_name;

Left join

Load

name,

count(name) as first_name_count

resident

imiona

Group by name;

Drop Table imiona;

Now it will give you correct o/p.

Regards

ASHFAQ

Re: Houston, we have a problem

Peter_Cammaert — Mon, 17 Nov 2014 13:49:37 GMT

Why make it so difficult? Let QlikView do the work for you!

Just add this below your LOAD *INLINE statement:

Basic_data:

CROSSTABLE (Type, Name)

LOAD last_name, name AS [As first name], sec_name AS [As second name]

RESIDENT imiona;

Then create a Pivot Table with dimensions Type and Name, and add expression =count(Name). Make Type into a horizontal dimension and... TADA!

Peter