topic Cross Table ,maybe in QlikView

Cross Table ,maybe

Mon, 28 Sep 2015 18:52:24 GMT

I have attached an excel file with 2 columns Region and Code.

Region Code

USA 1.0

MA 1.11

CA 1.21

OH 1.31

AUS 2.0

AD 2.11

WE 2.21

RG 2.31

From the above table I want to achieve 2 columns.COUNTRY and STATES

If code is 1.0 and 2.0 then USA,AUS.

Basically ,how can I achieve the below table from the above ?

Country States

USA MA

USA CA

USA OH

AUS AD

AUS WE

AUS RG

Thank you very much.

Re: Cross Table ,maybe

sunny_talwar — Mon, 28 Sep 2015 21:44:15 GMT

May be this:

Table:

LOAD * Inline [

Region, Code

USA, 1.0

MA, 1.11

CA, 1.21

OH, 1.31

AUS, 2.0

AD, 2.11

WE, 2.21

RG, 2.31

];

NewTable:

NoConcatenate

LOAD Region as Country,

Code

Resident Table

Where Len(PurgeChar(SubField(Code, '.', 2), '0')) = 0;

Join(NewTable)

LOAD Region as State,

SubField(Code, '.', 1) as Code

Resident Table

Where not Len(PurgeChar(SubField(Code, '.', 2), '0')) = 0;

DROP Table Table;

Output in a table box object:

Re: Cross Table ,maybe

MarcoWedel — Mon, 28 Sep 2015 23:27:05 GMT

Hi,

another solution could be:

tabRegions:
LOAD Region as Country,
    Code
FROM [https://community.qlik.com/servlet/JiveServlet/download/876386-188111/Test.xlsx] (ooxml, embedded labels, table is Sheet1);

Right Join (tabRegions)
LOAD Floor(Code) as Code,
    Country as States
Resident tabRegions
Where Frac(Code);

hope this helps

regards

Marco

Re: Cross Table ,maybe

Wed, 30 Sep 2015 11:55:33 GMT

MarcoWedel‌

Hi Marco,

This really worked for me.Thank you very much.

I have a small question.

So,If I have a code for state 2.1.1 or 5.12.3 or 3.6.14 etc what can I do ?

Thank you very much.