Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jessiemabalhin
New Contributor II

Complicated calculated fields

LocationNameColumn1Column2First Order
Orange - Services NSW (0088)221
Botany Services NSW (RTA-9315)331
Nowra Motor Registry (0086)441
Chatswood Motor Registry (1318-0024)551
Beverly Hills Motor Registry661
Five  Dock Motor Registry (RTA-0038)771
Miranda - Services NSW (5360)881
CASTLE HILL Services NSW (RTA-9273)991
Toukley Services NSW (RTA-0351)10101
Newcastle - Services NSW (1511)11111
Charlestown Motor Registry 12121
Lidcombe Motor Registry (RTA-0057)13131
MARRICKVILLE Services NSW (RTA-0066)14141
Ryde Services NSW (1525)15151
Toronto Motor Registry (0108)16161
Bondi Services NSW (RTA-9314)17171
Botany Services NSW (RTA-9315)1830
Muswellbrook Motor Registry (0076)19191
Wynyard Services NSW (1509)20201
Miranda - Services NSW (5360)2180
Tamworth Motor Registry 22221

How can I handle this scenario?

Column1 - is a calculated field that puts the row number as the value.

Column2 - is another calculated field that puts the row number of the first instance the LocationName.

                 Take row 18 'Botany Services NSW (RTA-9314)', the first instance can be found on row 3. That is why Column2 is set with 3.

FirstOrder - is another calculated field based on Column1 and Column2.

What would be the best approach for this?

P.S. - there are more fields before LocationName, I just trimmed it down and pasted it here.

       - I am trying to convert something from Excel into Qlik so all the functions used for Column1, Column2, and FirstOrder are Excel-based. 

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Complicated calculated fields

Like Attached?

Test:
Load LocationName,
RowNo()+1 as Column1;

LOAD * Inline
[
LocationName
Orange - Services NSW (0088)
Botany Services NSW (RTA-9315)
Nowra Motor Registry (0086)
Chatswood Motor Registry (1318-0024)
Beverly Hills Motor Registry
Five  Dock Motor Registry (RTA-0038)
Miranda - Services NSW (5360)
CASTLE HILL Services NSW (RTA-9273)
Toukley Services NSW (RTA-0351)
Newcastle - Services NSW (1511)
Charlestown Motor Registry
Lidcombe Motor Registry (RTA-0057)
MARRICKVILLE Services NSW (RTA-0066)
Ryde Services NSW (1525)
Toronto Motor Registry (0108)
Bondi Services NSW (RTA-9314)
Botany Services NSW (RTA-9315)
Muswellbrook Motor Registry (0076)
Wynyard Services NSW (1509)
Miranda - Services NSW (5360)
Tamworth Motor Registry
];

Map_RowNo:
Mapping Load
LocationName,
Column1
Resident Test;

FinalTable:
NoConcatenate Load
LocationName,
Column1,
ApplyMap('Map_RowNo',LocationName,'-1') as Column2,
if(Column1=ApplyMap('Map_RowNo',LocationName,'-1'),1,0) as FirstOrder
Resident Test
order by Column1;

drop Table Test;

4 Replies
sasiparupudi1
Honored Contributor III

Re: Complicated calculated fields

Like Attached?

Test:
Load LocationName,
RowNo()+1 as Column1;

LOAD * Inline
[
LocationName
Orange - Services NSW (0088)
Botany Services NSW (RTA-9315)
Nowra Motor Registry (0086)
Chatswood Motor Registry (1318-0024)
Beverly Hills Motor Registry
Five  Dock Motor Registry (RTA-0038)
Miranda - Services NSW (5360)
CASTLE HILL Services NSW (RTA-9273)
Toukley Services NSW (RTA-0351)
Newcastle - Services NSW (1511)
Charlestown Motor Registry
Lidcombe Motor Registry (RTA-0057)
MARRICKVILLE Services NSW (RTA-0066)
Ryde Services NSW (1525)
Toronto Motor Registry (0108)
Bondi Services NSW (RTA-9314)
Botany Services NSW (RTA-9315)
Muswellbrook Motor Registry (0076)
Wynyard Services NSW (1509)
Miranda - Services NSW (5360)
Tamworth Motor Registry
];

Map_RowNo:
Mapping Load
LocationName,
Column1
Resident Test;

FinalTable:
NoConcatenate Load
LocationName,
Column1,
ApplyMap('Map_RowNo',LocationName,'-1') as Column2,
if(Column1=ApplyMap('Map_RowNo',LocationName,'-1'),1,0) as FirstOrder
Resident Test
order by Column1;

drop Table Test;

MVP
MVP

Re: Complicated calculated fields

Is this something you need in the script or front end?

jessiemabalhin
New Contributor II

Re: Complicated calculated fields

Hi Sunny, this would be in the script.

jessiemabalhin
New Contributor II

Re: Complicated calculated fields

this is exactly what I need..thanks!!