Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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
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.
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.
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;
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.
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.
Thanks guys.
The Date function on the field worked.
Thanks heaps.
Regards,
Shyam.
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.