Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date into Number

Hi

How can i convert Date values into number format.

I used below fn but no luck. How can i convert.

NUM(date("DISTRIBUTED_DT",'YYYYMMDD'))

MY number should be YEARMONTHDATE e.g 20160817

1 Solution

Accepted Solutions
sunny_talwar

My friend, why are you not adding the DISTRIBUTED_DT in your SELECT statement? Unless you do, you will continue to get this error

[EXP_F_EXPENSE]:

LOAD*,

  Num#(Date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY;

SELECT "RECORD_DATE_KEY",

  "COMPANY_KEY",

  "ACFT_KEY",

  "AIRCRAFT_UNIT_KEY",

  "GL_ACCOUNT_KEY",

  "INV_KEY",

  "INV_LINE_ITEM_KEY",

  "FLEET_KEY",

  "LOCATION_KEY",

  "FACT_KEY",

  "ORD_LINE_ITEM_KEY",

  "SERVICE_LOCATION",

  "SUPPLIER_LOCATION",

  "ORD_KEY",

  "SUP_ITEM_KEY",

  "ATA_KEY",

  "SUP_KEY",

  "EXP_CLASS_CD",

  "EXP_DIVISION_CD",

  "EXP_COST_ROLLUP_CD",

  "EXP_COST_ACCOUNT_CD",

  "EXP_COST_SUBCATEGORY_CD",

  "INVOICED_RATE",

  "INVOICED_QUANTITY",

  "INVOICE_LINE_TOTAL",

  "DISPUTED_AMOUNT",

  "CLAIM_LINE_ITEM_SUM",

  "DEBIT_LINE_ITEM_SUM",

  "CREDIT_LINE_ITEM_SUM",

  "ACTUAL_EXPENSE",

  "ACCRUAL_EXPENSE",

  "GRAND_TOTAL_EXPENSE",

  "ACCRUAL_IND",

  //Num("DISTRIBUTED_DT") AS DATE_KEY,

   DISTRIBUTED_DT,

    "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

Please add the field in red above, else you will continue to get the error

View solution in original post

16 Replies
narendiran
Partner - Creator
Partner - Creator

Hi Govind,

Try this

=Num([DISTRIBUTED_DT])

sunny_talwar

May be this:

Num#(Date(DISTRIBUTED_DT, 'YYYYMMDD')) as YEARMONTHDATE

Not applicable
Author

Select

"DEBIT_LINE_ITEM_SUM",

"CREDIT_LINE_ITEM_SUM",
"ACTUAL_EXPENSE",
"ACCRUAL_EXPENSE",
"GRAND_TOTAL_EXPENSE",
"ACCRUAL_IND",
NUM#(date("DISTRIBUTED_DT",'YYYYMMDD')) AS "DATE_KEY",

    "DISTRIBUTED_DT",

"APPROVED_DT"

FROM "EXP_F_EXPENSE";

Showing Error. I given like above.Please check

Not applicable
Author

The following error occurred:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

sunny_talwar

The operation won't work in SQL Select statement. This will need to be carried out in a Load statement....


Load ....

TransformationHere;

Select

....

From ....

Not applicable
Author

Please give me steps.

My table

[EXP_F_EXPENSE]:

SELECT "RECORD_DATE_KEY",

  "COMPANY_KEY",

  "ACFT_KEY",

  "AIRCRAFT_UNIT_KEY",

  "GL_ACCOUNT_KEY",

  "INV_KEY",

  "INV_LINE_ITEM_KEY",

  "FLEET_KEY",

  "LOCATION_KEY",

  "FACT_SK",

  "ORD_LINE_ITEM_KEY",

  "SERVICE_LOCATION",

  "SUPPLIER_LOCATION",

  "ORD_KEY",

  "SUP_ITEM_KEY",

  "ATA_KEY",

  "SUP_KEY",

  "EXP_CLASS_CD",

  "EXP_DIVISION_CD",

  "EXP_COST_ROLLUP_CD",

  "EXP_COST_ACCOUNT_CD",

  "EXP_COST_SUBCATEGORY_CD",

  "INVOICED_RATE",

  "INVOICED_QUANTITY",

  "INVOICE_LINE_TOTAL",

  "DISPUTED_AMOUNT",

  "CLAIM_LINE_ITEM_SUM",

  "DEBIT_LINE_ITEM_SUM",

  "CREDIT_LINE_ITEM_SUM",

  "ACTUAL_EXPENSE",

  "ACCRUAL_EXPENSE",

  "GRAND_TOTAL_EXPENSE",

  "ACCRUAL_IND",

  //Num#(date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY,

    "DISTRIBUTED_DT" AS DATE_KEY,

  "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

sunny_talwar

Try like this:

[EXP_F_EXPENSE]:

LOAD *,

          Num#(Date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY;

SELECT "RECORD_DATE_KEY",

  "COMPANY_KEY",

  "ACFT_KEY",

  "AIRCRAFT_UNIT_KEY",

  "GL_ACCOUNT_KEY",

  "INV_KEY",

  "INV_LINE_ITEM_KEY",

  "FLEET_KEY",

  "LOCATION_KEY",

  "FACT_SK",

  "ORD_LINE_ITEM_KEY",

  "SERVICE_LOCATION",

  "SUPPLIER_LOCATION",

  "ORD_KEY",

  "SUP_ITEM_KEY",

  "ATA_KEY",

  "SUP_KEY",

  "EXP_CLASS_CD",

  "EXP_DIVISION_CD",

  "EXP_COST_ROLLUP_CD",

  "EXP_COST_ACCOUNT_CD",

  "EXP_COST_SUBCATEGORY_CD",

  "INVOICED_RATE",

  "INVOICED_QUANTITY",

  "INVOICE_LINE_TOTAL",

  "DISPUTED_AMOUNT",

  "CLAIM_LINE_ITEM_SUM",

  "DEBIT_LINE_ITEM_SUM",

  "CREDIT_LINE_ITEM_SUM",

  "ACTUAL_EXPENSE",

  "ACCRUAL_EXPENSE",

  "GRAND_TOTAL_EXPENSE",

  "ACCRUAL_IND",

    "DISTRIBUTED_DT",

  "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

its_anandrjs

Try this way also

Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD')

Or

NUM( Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD') )

Regards

Anand

Not applicable
Author

The following error occurred:

Field not found - <DISTRIBUTED_DT>