Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shyamcharan
Creator III
Creator III

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

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

10 Replies
swuehl
MVP
MVP

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
Creator III
Creator III
Author

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
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III
Author

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

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jagan
Luminary Alumni
Luminary Alumni

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
MVP
MVP

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
Creator III
Creator III
Author

Thanks guys.

The Date function on the field worked.

Thanks heaps.

Regards,

Shyam.

shyamcharan
Creator III
Creator III
Author

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.