Houston, we have a problem

[#1]

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

[#2]

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;

[#3]

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

[ashfaq_haseeb · Accepted solution]

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

[Peter_Cammaert]

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