topic [ASK] join 2 column into 1 in QlikView

[ASK] join 2 column into 1

Thu, 05 Apr 2012 09:26:49 GMT

hello qlikview master..

i wanna ask a question, simple but complicated

so i have an excel files with data format like below :

id	school_name	male_gender	female_gender
1	school A	4	6
2	school B	3	5
3	school C	5	7
4	school D	13	19

and i suppose to make the data format like below in qlikview :

id	school_name	gender	total
1	school A	male	4
1	school A	female	6
2	school B	male	3
2	school B	female	5
3	school C	male	5
3	school C	female	7
4	school D	male	13
4	school D	female	19

and i have thousands of datas that makes me impossible to do it manually one-by-one

how to do that in qlikview?

many thx...

[ASK] join 2 column into 1

jagan — Thu, 05 Apr 2012 09:32:08 GMT

Hi,

Try like this

Data:

LOAD

id,

school-name,

'male' AS gender,

total

FROM DataSource

Where gender = 'male';

CONCATENATE(Data)

Data:

LOAD

id,

school-name,

'female' AS gender,

total

FROM DataSource

Where gender = 'female';

Now you have columns id, school-name, gender and total as you required.

Hope this helps you.

Regards,

Jagan.

Re: [ASK] join 2 column into 1

Thu, 05 Apr 2012 09:36:10 GMT

you mention 'total' column, where is it come from?

because there is no 'total' column in data-source

many thx

==========

edited

i think i know what do you mean

let me give it a shoot

[ASK] join 2 column into 1

jagan — Thu, 05 Apr 2012 09:39:00 GMT

Hi,

Use this script

Data:

LOAD

id,

school-name,

'male' AS gender,

'male_gender' AS total

FROM DataSource

Where gender = 'male';

CONCATENATE(Data)

Data:

LOAD

id,

school-name,

'female' AS gender,

'female_gender' AS total

FROM DataSource

Where gender = 'female';

Regards,

Jagan.

Re: [ASK] join 2 column into 1

Thu, 05 Apr 2012 09:42:49 GMT

Data:

LOAD

id,

school-name,

'male' AS gender,

male_gender as total

FROM DataSource;

CONCATENATE(Data)

Data:

LOAD

id,

school-name,

'female' AS gender,

female_gender as total

FROM DataSource';

many thx jagan

[ASK] join 2 column into 1

jagan — Thu, 05 Apr 2012 10:39:42 GMT

Hi,

You are correct, where condition should be removed. I posted it in a bit hurry.

Regards,

Jagan.