# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Complicated calculated fields

 LocationName Column1 Column2 First Order Orange - Services NSW (0088) 2 2 1 Botany Services NSW (RTA-9315) 3 3 1 Nowra Motor Registry (0086) 4 4 1 Chatswood Motor Registry (1318-0024) 5 5 1 Beverly Hills Motor Registry 6 6 1 Five  Dock Motor Registry (RTA-0038) 7 7 1 Miranda - Services NSW (5360) 8 8 1 CASTLE HILL Services NSW (RTA-9273) 9 9 1 Toukley Services NSW (RTA-0351) 10 10 1 Newcastle - Services NSW (1511) 11 11 1 Charlestown Motor Registry 12 12 1 Lidcombe Motor Registry (RTA-0057) 13 13 1 MARRICKVILLE Services NSW (RTA-0066) 14 14 1 Ryde Services NSW (1525) 15 15 1 Toronto Motor Registry (0108) 16 16 1 Bondi Services NSW (RTA-9314) 17 17 1 Botany Services NSW (RTA-9315) 18 3 0 Muswellbrook Motor Registry (0076) 19 19 1 Wynyard Services NSW (1509) 20 20 1 Miranda - Services NSW (5360) 21 8 0 Tamworth Motor Registry 22 22 1

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
Honored Contributor III

## Re: Complicated calculated fields

Like Attached?

Test:
RowNo()+1 as Column1;

[
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:
LocationName,
Column1
Resident Test;

FinalTable:
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
Honored Contributor III

## Re: Complicated calculated fields

Like Attached?

Test:
RowNo()+1 as Column1;

[
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:
LocationName,
Column1
Resident Test;

FinalTable:
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

## Re: Complicated calculated fields

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

New Contributor II

## Re: Complicated calculated fields

Hi Sunny, this would be in the script.

New Contributor II

## Re: Complicated calculated fields

this is exactly what I need..thanks!!