topic Re: Original value display after Autonumber() in QlikView

Original value display after Autonumber()

shyamcharan — Tue, 16 Feb 2016 00:00:22 GMT

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.

Re: Original value display after Autonumber()

swuehl — Tue, 16 Feb 2016 00:20:14 GMT

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.

Re: Original value display after Autonumber()

shyamcharan — Tue, 16 Feb 2016 00:55:01 GMT

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

Re: Original value display after Autonumber()

jagan — Tue, 16 Feb 2016 01:32:51 GMT

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.

Re: Original value display after Autonumber()

shyamcharan — Tue, 16 Feb 2016 05:54:21 GMT

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.

Re: Original value display after Autonumber()

MayilVahanan — Tue, 16 Feb 2016 06:03:12 GMT

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;

Re: Original value display after Autonumber()

jagan — Tue, 16 Feb 2016 06:08:44 GMT

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.

Re: Original value display after Autonumber()

swuehl — Tue, 16 Feb 2016 09:53:57 GMT

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.

Re: Original value display after Autonumber()

shyamcharan — Thu, 18 Feb 2016 02:26:45 GMT

Thanks guys.

The Date function on the field worked.

Thanks heaps.

Regards,

Shyam.

Re: Original value display after Autonumber()

shyamcharan — Thu, 18 Feb 2016 04:05:14 GMT

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.

Re: Original value display after Autonumber()

MayilVahanan — Thu, 18 Feb 2016 06:50:36 GMT

Character fields comes as it is like database..

If you need to convert to string, you can use Text() function