[ASK] join 2 column into 1

[#1]

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...

[jagan · Accepted solution]

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.

[#3]

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

[jagan]

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.

[#5]

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

[jagan]

Hi,

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

Regards,

Jagan.