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: 
Not applicable

Table Aliases in LOAD statement?

I have a table which holds data about enquiries. Each Enquiry has an ID. Each Enquiry can have several follow ups and each has their own row in the same table with a sequential sequence number for that Enquiry. Each Row has a timestamp when it was inserted into the Database.

I want to create a table In QlikView that will hold only one row for each Enquiry ID, that will also have a column which will hold the TimeStamp record from the Sequence number 0 row for that enquiry, and another column that will store the timestamp record from the sequence number 1 row for that same enquiry.

If I was trying to do this in an SQL statement I would just use table aliases, but I am unsure of if you can do this in QlikView or the syntax if you can. I have tried the following:


CTS_ENQUIRIES_RESPONSETIME:
BUFFER
(Stale After 24 Hours)
LOAD
Distinct If(SEQUENCENO=0,ENQUIRYID) As ENQUIRY,
If (SEQUENCENO=0, TIMESTARTED) AS "LOGDATE",
If(SEQUENCENO=1, TIMESTARTED) AS "FOLLOWUPDATE";

SQL
SELECT * FROM CRMUSER."CTS_ENQUIRIES2";


But as I expected before even trying it I get the following table structure with the two timestamp dates on different rows.

error loading image

Does anyone know a simple solution to this? I am sure there is one! Can you use a while loop in a load statement to build a table?

Thanks
Barry

1 Solution

Accepted Solutions
Not applicable
Author

If you know how to write the query you want in SQL, why not write it in SQL? Just replace the SQL SELECT statement in your screen shot above, then you can do everything you know how to do in SQL, without having to fiddle with QV syntax.

View solution in original post

3 Replies
Not applicable
Author

Hi Barry,

I'm not sure if this will work - you may need to load the data into a qvd file first - but you should be able to do this using Join.

eg:


Load
Distinct If (SEQUENCENO=0, ENQUIRYID) As Enquiry,
If (SEQUENCENO=0, TIMESTARTED) As "LOGDATE";
JOIN
Load
Distinct If (SEQUENCENO=1, ENQUIRYID) As Enquiry,
If (SEQUENCENO=1, TIMESTARTED) As "FOLLOWUPDATE";


This should join the data on the (matching) Enquiry field.

hth,

Tony

Not applicable
Author

If you know how to write the query you want in SQL, why not write it in SQL? Just replace the SQL SELECT statement in your screen shot above, then you can do everything you know how to do in SQL, without having to fiddle with QV syntax.

Not applicable
Author

As usual, I needed the obvious pointed out to me. I was lost in loops of scripting etc and it never dawned on me to pull out the data as I needed it via the SQL statement.

Whoops Embarrassed