# View: int_prod_performance_rights_historic_accounting

**View Name:** int_prod_performance_rights_historic_accounting
**Table Source:** `Derived from: Clean_Table_1, Clean_Table_2, Prod.Performance_Rights_Historic_Accounting`
**File Path:** `int_prod_performance_rights_historic_accounting.view.lkml`

## Overview

- **File Size:** 9664 bytes
- **Lines of Code:** 286
- **Dimensions:** 31
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `row_id` | number |
| `territory` | string |
| `society` | string |
| `type` | string |
| `dms_issued_id` | string |
| `society_artist_name` | string |
| `society_track_name` | string |
| `society_release_name` | string |
| `society_label_name` | string |
| `society_upc` | string |
| `label_name` | string |
| `label_id` | number |
| `imprint` | string |
| `subaccount_name` | string |
| `subaccount_id` | string |
| `oa_artist` | string |
| `oa_track` | string |
| `oa_release` | string |
| `oa_upc` | string |
| `good_tuid` | number |
| `reporting_soruce` | string |
| `period_month` | string |
| `period_quarter` | string |
| `period_year` | string |
| `matched_on` | string |
| `notes` | string |
| `match_code` | string |
| `process` | string |
| `oa_isrc` | string |
| `society_isrc` | string |
| `isrcdata` | string |

## Measures

| Name | Type |
|------|------|
| `gross_amount` | sum |

## SQL Comments

- do not use holy shit its bad

## Derived Table

```sql
sql: with clean_table_1 as (select iff(row_id = 'NA', null, row_id) as ROW_ID,
              iff(TERRITORY = 'NA', null, TERRITORY) as TERRITORY,
              iff(SOCIETY = 'NA', null, SOCIETY) as SOCIETY,
              iff(TYPE = 'NA', null, TYPE) as TYPE,
              iff(DMS_ISSUED_ID = 'NA', null, DMS_ISSUED_ID) as DMS_ISSUED_ID,
              iff(SOCIETY_ARTIST_NAME = 'NA', null, SOCIETY_ARTIST_NAME) as SOCIETY_ARTIST_NAME,
              iff(SOCIETY_TRACK_NAME = 'NA', null, SOCIETY_TRACK_NAME) as SOCIETY_TRACK_NAME,
              iff(SOCIETY_RELEASE_NAME = 'NA', null, SOCIETY_RELEASE_NAME) as SOCIETY_RELEASE_NAME,
              iff(SOCIETY_LABEL_NAME = 'NA', null, SOCIETY_LABEL_NAME) as SOCIETY_LABEL_NAME,
              iff(SOCIETY_ISRC = 'NA', null, SOCIETY_ISRC) as SOCIETY_ISRC,
              iff(SOCIETY_UPC = 'NA', null, SOCIETY_UPC) as SOCIETY_UPC,
              iff(LABEL_ID = 'NA', null, LABEL_ID) as LABEL_ID,
              iff(LABEL_NAME = 'NA', null, LABEL_NAME) as LABEL_NAME,
              iff(IMPRINT = 'NA', null, IMPRINT) as IMPRINT,
              iff(SUBACCOUNT_NAME = 'NA', null, SUBACCOUNT_NAME) as SUBACCOUNT_NAME,
              iff(SUBACCOUNT_ID = 'NA', null, SUBACCOUNT_ID) as SUBACCOUNT_ID,
              iff(OA_ARTIST = 'NA', null, OA_ARTIST) as OA_ARTIST,
              iff(OA_TRACK = 'NA', null, OA_TRACK) as OA_TRACK,
              iff(OA_RELEASE = 'NA', null, OA_RELEASE) as OA_RELEASE,
              iff(OA_ISRC = 'NA', null, OA_ISRC) as OA_ISRC,
              iff(OA_UPC = 'NA', null, OA_UPC) as OA_UPC,
              iff(GOOD_TUID = 'NA', null, GOOD_TUID) as GOOD_TUID,
              iff(GROSS_AMOUNT = 'NA', null, GROSS_AMOUNT) as GROSS_AMOUNT,
              iff(REPORTING_SOURCE = 'NA', null, REPORTING_SOURCE) as REPORTING_SOURCE,
              iff(PERIOD_MONTH = 'NA', null, PERIOD_MONTH) as PERIOD_MONTH,
              iff(PERIOD_QUARTER = 'NA', null, PERIOD_QUARTER) as PERIOD_QUARTER,
              iff(PERIOD_YEAR = 'NA', null, PERIOD_YEAR) as PERIOD_YEAR,
              iff(MATCHED_ON = 'NA', null, MATCHED_ON) as MATCHED_ON,
              iff(NOTES = 'NA', null, NOTES) as NOTES,
              iff(MATCH_CODE = 'NA', null, MATCH_CODE) as MATCH_CODE,
              iff(PROCESS = 'NA', null, PROCESS) as PROCESS
             from INTELLIGENCE.PROD.PERFORMANCE_RIGHTS_HISTORIC_ACCOUNTING
            where gross_amount not in ('"GTA010702632"', '"GTA010703084"', '"US38L0901377"', '"GTA010702624"',
                                       '"USEXZ1001015"', '"USUSS1011854"', '"GTA010703232"', '""', '"GTA010702638"',
                                       '"US4EB0502007"', '"GTA010702629"', '"ITO100910538"', '"GTA010702627"',
                                       '"USUSS1011853"', '"GTA010703097"', '"GTA010702630"','"USUSS1011851"',
                                       '"USUSS1011841"', '"GTA010702637"', '"GTA010702635"', '"USUSS1011842"',
                                       '"US2H51200146"', '"Gross_Amount"', '"USUSS1011845"','"GTA010703049"',
                                       '"US25T9919103"', '"GTA010703089"', '"GTA010702628"', '"ES5300003569"',
                                       '"US2Y30601582"', '"USUSS1011852"', '"GTA010702636"', '"GTA010702639"',
                                       '"US2Y30601584"', '"USUSS1011850"', '"US4EB0803902"', '"USA560841022"',
                                       '"ES6340900435"')),


clean_table_2 as (Select try_to_number(LEFT(RIGHT(ROW_ID, len(ROW_ID)-1),len(ROW_ID)-2)) as ROW_ID,
LEFT(RIGHT(TERRITORY, len(TERRITORY)-1),len(TERRITORY)-2) as TERRITORY,
LEFT(RIGHT(SOCIETY, len(SOCIETY)-1),len(SOCIETY)-2) as SOCIETY,
LEFT(RIGHT(TYPE, len(TYPE)-1),len(TYPE)-2) as TYPE,
LEFT(RIGHT(DMS_ISSUED_ID, len(DMS_ISSUED_ID)-1),len(DMS_ISSUED_ID)-2) as DMS_ISSUED_ID,
LEFT(RIGHT(SOCIETY_ARTIST_NAME, len(SOCIETY_ARTIST_NAME)-1),len(SOCIETY_ARTIST_NAME)-2) as SOCIETY_ARTIST_NAME,
LEFT(RIGHT(SOCIETY_TRACK_NAME, len(SOCIETY_TRACK_NAME)-1),len(SOCIETY_TRACK_NAME)-2) as SOCIETY_TRACK_NAME,
LEFT(RIGHT(SOCIETY_RELEASE_NAME, len(SOCIETY_RELEASE_NAME)-1),len(SOCIETY_RELEASE_NAME)-2) as SOCIETY_RELEASE_NAME,
LEFT(RIGHT(SOCIETY_LABEL_NAME, len(SOCIETY_LABEL_NAME)-1),len(SOCIETY_LABEL_NAME)-2) as SOCIETY_LABEL_NAME,
LEFT(RIGHT(SOCIETY_ISRC, len(SOCIETY_ISRC)-1),len(SOCIETY_ISRC)-2) as SOCIETY_ISRC,
LEFT(RIGHT(SOCIETY_UPC, len(SOCIETY_UPC)-1),len(SOCIETY_UPC)-2) as SOCIETY_UPC,
try_to_number(LEFT(RIGHT(LABEL_ID, len(LABEL_ID)-1),len(LABEL_ID)-2)) as LABEL_ID,
LEFT(RIGHT(LABEL_NAME, len(LABEL_NAME)-1),len(LABEL_NAME)-2) as LABEL_NAME,
LEFT(RIGHT(IMPRINT, len(IMPRINT)-1),len(IMPRINT)-2) as IMPRINT,
LEFT(RIGHT(SUBACCOUNT_NAME, len(SUBACCOUNT_NAME)-1),len(SUBACCOUNT_NAME)-2) as SUBACCOUNT_NAME,
try_to_number(LEFT(RIGHT(SUBACCOUNT_ID, len(SUBACCOUNT_ID)-1),len(SUBACCOUNT_ID)-2)) as SUBACCOUNT_ID,
LEFT(RIGHT(OA_ARTIST, len(OA_ARTIST)-1),len(OA_ARTIST)-2) as OA_ARTIST,
LEFT(RIGHT(OA_TRACK, len(OA_TRACK)-1),len(OA_TRACK)-2) as OA_TRACK,
LEFT(RIGHT(OA_RELEASE, len(OA_RELEASE)-1),len(OA_RELEASE)-2) as OA_RELEASE,
LEFT(RIGHT(OA_ISRC, len(OA_ISRC)-1),len(OA_ISRC)-2) as OA_ISRC,
LEFT(RIGHT(OA_UPC, len(OA_UPC)-1),len(OA_UPC)-2) as OA_UPC,
try_to_number(LEFT(RIGHT(GOOD_TUID, len(GOOD_TUID)-1),len(GOOD_TUID)-2)) as GOOD_TUID,
GROSS_AMOUNT,
LEFT(RIGHT(REPORTING_SOURCE, len(REPORTING_SOURCE)-1),len(REPORTING_SOURCE)-2) as REPORTING_SOURCE,
LEFT(RIGHT(PERIOD_MONTH, len(PERIOD_MONTH)-1),len(PERIOD_MONTH)-2) as PERIOD_MONTH,
LEFT(RIGHT(PERIOD_QUARTER, len(PERIOD_QUARTER)-1),len(PERIOD_QUARTER)-2) as PERIOD_QUARTER, -- do not use holy shit its bad
LEFT(RIGHT(PERIOD_YEAR, len(PERIOD_YEAR)-1),len(PERIOD_YEAR)-2) as PERIOD_YEAR,
LEFT(RIGHT(MATCHED_ON, len(MATCHED_ON)-1),len(MATCHED_ON)-2) as MATCHED_ON,
LEFT(RIGHT(NOTES, len(NOTES)-1),len(NOTES)-2) as NOTES,
LEFT(RIGHT(MATCH_CODE, len(MATCH_CODE)-1),len(MATCH_CODE)-2) as MATCH_CODE,
LEFT(RIGHT(PROCESS, len(PROCESS)-1),len(PROCESS)-2) as PROCESS
from clean_table_1)

select *, CASE
            WHEN society_isrc IS NOT NULL AND society_isrc != 'NULL'
            THEN replace(society_isrc, ' ','')
            WHEN oa_isrc like '%Available%'
            THEN NULL
            ELSE replace(oa_isrc, ' ','')
        END as isrcdata
from clean_table_2;;
```