Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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:
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
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.