Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

shyamcharan
Not applicable

Original value display after Autonumber()

Hi Experts,

I have a key column which has a string values. I converted it to numeric key column using the Autonumber() function.

However, when I try to display the original column values, I could not get it displayed.

Please check the sample document attached.

I was able to display the original values of the column only after RESIDENT Loading but not normal load from QVD.

Isn't there a better of doing it?

How to display the original values of the column that is used in Autonumber function?

Any help here is highly appreicated.

Regards,

Shyam.

1 Solution

Accepted Solutions
mayilvahanan
Not applicable

Re: Original value display after Autonumber()

HI

Try like this

LOAD  Activity_Master_ID,

      Autonumber(IF(Len(Trim(RMI_PK))=0,0,RMI_PK)) AS RMI_PK_ID,

Autonumber(IF(Len(Trim(RVA_PK))=0,0,RVA_PK)) AS RVA_PK_ID,    

Autonumber(IF(Len(Trim(RT_PK))=0,0,RT_PK)) AS RT_PK_ID,

Autonumber(IF(Len(Trim(Date_Response))=0,0,Date_Response)) AS Date_Id,

  Date(Date_Response) AS Actual_Date_Response    

     FROM

YourFileName;

10 Replies
swuehl
Not applicable

Re: Original value display after Autonumber()

Not sure what you mean with 'when I try to display the original column values'.

On your first script tab:

LOAD * INLINE [
    O1, O4
    A123, NorthSydney
    E123, Townhall
    F123, Katumba
];

T2:
Load AutoNumber(O1,'O1') as C1, O1 as T2O1, O4 AS C4
Resident OTD2;
//Drop Table TD2;

OTD3:
LOAD * INLINE [
    O5, O4
    Northern place, NorthSydney
    City CBD, Townhall
    Rural Region, Katumba
];

T3:
Load AutoNumber(O4,'O4') as C4, O4 as T3O4, O5 AS C5
Resident OTD3;
//Drop Table TD3;

Here, you created key C4 one time using AutoNumber, one time without.

On your second tab:

CADM_Activity_Master:

LOAD  Activity_Master_ID,

      Autonumber(IF(Len(Trim(RMI_PK))=0,0,RMI_PK)) AS RMI_PK_ID,

Autonumber(IF(Len(Trim(RVA_PK))=0,0,RVA_PK)) AS RVA_PK_ID,    

Autonumber(IF(Len(Trim(RT_PK))=0,0,RT_PK)) AS RT_PK_ID,

Autonumber(IF(Len(Trim(Date_Response))=0,0,Date_Response)) AS Date_Id,

  Date_Response AS Actual_Date_Response    

     Resident AM;

   

You are using AutoNumber() without the optional second argument for an AutoID, so the numbers for all three ID fields will be using the same 'bucket' of sequential numbers to count up.

Maybe these two findings might help you in fixing your issues.

shyamcharan
Not applicable

Re: Original value display after Autonumber()

Hi swuehl,

Thank your response.

Sorry, in hurry I forgot to remove the first tab code.

Please check the QVW attached to this reply. I am loading a few columns from the QVD.

However, I have used Autonumber function on Date_Response column to join to other table.

Now, I want the same column original values since I would have to display in the dashboard.

Hence, I have included the same column name below the Autonumber() function column and given a different name.

But, I get the number not the original dates in the display.

Also, please let me know if using the optional second argument is mandatory. I have used more than 10 fields from different tables as Autonumber() columns to join them.


Will this be a problem going forward?

Thanks in advance.

--Shyam

jagan
Not applicable

Re: Original value display after Autonumber()

Hi,

If you want to retain the original values you have to use the additional column with alias like below

LOAD

AutoNumber(Date_Response ) AS Date_Response ,

Date_Response  AS Date_Response_Orig,

'

'

'

FROM DataSource;


Now use Date_Response_Orig column to display the actual values.


Hope this helps you.


Regards,

jagan.

shyamcharan
Not applicable

Re: Original value display after Autonumber()

Hi Jagan,

Thanks for the response.

I have tried that and it is in my previous post QVW document.

However, it did not work.

I appreciate further help on this.

Regards,

shyam.

mayilvahanan
Not applicable

Re: Original value display after Autonumber()

HI

Try like this

LOAD  Activity_Master_ID,

      Autonumber(IF(Len(Trim(RMI_PK))=0,0,RMI_PK)) AS RMI_PK_ID,

Autonumber(IF(Len(Trim(RVA_PK))=0,0,RVA_PK)) AS RVA_PK_ID,    

Autonumber(IF(Len(Trim(RT_PK))=0,0,RT_PK)) AS RT_PK_ID,

Autonumber(IF(Len(Trim(Date_Response))=0,0,Date_Response)) AS Date_Id,

  Date(Date_Response) AS Actual_Date_Response    

     FROM

YourFileName;

jagan
Not applicable

Re: Original value display after Autonumber()

Hi,

Is this not working

CADM_Activity_Master:

LOAD  Activity_Master_ID,

      Autonumber(IF(Len(Trim(RMI_PK))=0,0,RMI_PK)) AS RMI_PK_ID,

Autonumber(IF(Len(Trim(RVA_PK))=0,0,RVA_PK)) AS RVA_PK_ID,    

Autonumber(IF(Len(Trim(RT_PK))=0,0,RT_PK)) AS RT_PK_ID,

Autonumber(IF(Len(Trim(Date_Response))=0,0,Date_Response)) AS Date_Id,

  Date_Response AS Actual_Date_Response    

     FROM

(qvd);

Actual_Date_Response      field not showing actual values?


Regards,

jagan.

swuehl
Not applicable

Re: Original value display after Autonumber()

Yes, I think you just need to reformat your date field using formatting function Date().

LOAD ...

        Date(Date_Response) AS Actual_Date_Response

...

You can add an additional format code to date function as needed.

I believe this has nothing to do with the autonumber, or are you saying that the date comes in formatted from the QVD when you are removing the autonumber()'ed ID fields in the LOAD?

To answer your other question: the second argument is not mandatory, you just won't get sequential numbering guaranteed per ID field.

shyamcharan
Not applicable

Re: Original value display after Autonumber()

Thanks guys.

The Date function on the field worked.

Thanks heaps.

Regards,

Shyam.

shyamcharan
Not applicable

Re: Original value display after Autonumber()

Guys,

Just a quick question, What if I have non Date field?

What functions should I use for Character fields?

thanks in advance.

Regards,

Shyam.