Cross Table ,maybe

[#1]

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.

[sunny_talwar]

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:

[MarcoWedel · Accepted solution]

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

[#4]

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.