Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raadwiptec
Creator II
Creator II

Few columns to Rows

hi all

I have a data as below in my database table

Contract No | Name | Address | Code1 | Code2 | Code3 | Code4 |

1                          xxxdf     dfdff           A             B                                D

2                           yyysd   tyyhj          B             C               A              A

 

I need the output as

Contract No | Name | Address | Code | 

1                          xxxdf    dfdff           A

1                          xxxdf    dfdff           B

1                          xxxdf    dfdff           C

1                          xxxdf    dfdff           D

2                           yyysd   tyyhj          B 

2                           yyysd   tyyhj          C

2                           yyysd   tyyhj          A

2                           yyysd   tyyhj          D

please suggest

3 Replies
SONIAGESIMDE
Contributor II
Contributor II

You could use:

crosstable (codex,CODE,3)
LOAD [Contract No ],
[Name ],
[Address ],
[Code1],
[Code2],
[Code3],
[Code4]
FROM [C:\Users\Sonia\Desktop\PRU.xlsx] (ooxml, embedded labels, table is Hoja1);

 

 

raadwiptec
Creator II
Creator II
Author

Hi Sonia

 

Do you know how to transpose multiple columns?

For Example : If have another 2 additional columns 

Risk   |  Allocation

1                    AA

11                  BB

22                  CC

33                  DD

SONIAGESIMDE
Contributor II
Contributor II

Hi,

Assuming that the table was like this:

ContractNoNameAddressCode1Code2Code3Code4RiskAllocation
1xxxdfdfdffAB D1AA
2yyysdtyyhjBCAA11BB

 

You could do the following:

crosstable (codex,CODE,3)
LOAD ContractNo,
Name,
Address,
Code1,
Code2,
Code3,
Code4
FROM [C:\Users\Sonia\Desktop\PRU.xlsx] (ooxml, embedded labels, table is Hoja1);

 

crosstable (risk,RISK,3)
LOAD ContractNo,
Name,
Address,
Risk
FROM
[C:\Users\Sonia\Desktop\PRU.xlsx]
(ooxml, embedded labels, table is Hoja1);


crosstable (Allocation,ALLOCATION,3)
LOAD ContractNo,
Name,
Address,
Allocation
FROM
[C:\Users\Sonia\Desktop\PRU.xlsx]
(ooxml, embedded labels, table is Hoja1);

And you would get:

ContractNoNameAddressCODERISKALLOCATION
1xxxdfdfdffA1AA
1xxxdfdfdffB1AA
1xxxdfdfdffD1AA
2yyysdtyyhjA11BB
2yyysdtyyhjB11BB
2yyysdtyyhjC11BB