topic Re: If statement causes QV crash on load in QlikView

If statement causes QV crash on load

Anonymous — Mon, 02 Jul 2012 16:15:03 GMT

Not sure if anyone will be able to answer this but I'm having some issues with an if statement in my load script. My script refreshes fine without the below statement but hangs and ultimately requires me to restart my computer when I include this the join with an if statement.

I'm trying to create a flag in one of my tables based on two fields that are loaded from different tables and joined into one table. The flag will indicate whether the current record has the same value in the second table as the record that was loaded from the first. There is a one to many relationship between the first and second tables. I wasn't able to successfully get the document to recognize the two fields (since they are coming from different tables) when included in the second statement until I moved it into a left join from the resident table. I'm not sure I have the best approach here so I was hoping someone could provide me with a suggestion of how to optimize the creation of this flag and hopefully eradicate my issues with QV crashing. Current load statement below - non-relevant fields were removed.

BCTable:

LOAD

"n_case_id" as BCID,

"n_version_id_latest_approved" as LatestApprovedVersionID;

//SQL statement not included here

Right Join (BCTable)

LOAD

"n_case_id" as BCID,

"n_version_id" as VersionID;

//SQL statement not included here

Left Join (BCTable)

LOAD If(VersionID=LatestApprovedVersionID,1,0) as LatestApprovedFlag

Resident BCTable;

Any help will be greatly appreciated!

Re: If statement causes QV crash on load

Mon, 02 Jul 2012 16:28:56 GMT

Hi Kavin:

I guess the issue because the third statement doesnt have any common fields with BCTable. Instead of left join, did you try the following?

LOAD BCID,VersionID,LatestApprovedVersionID,
If(VersionID=LatestApprovedVersionID,1,0) as LatestApprovedFlag
Resident BCTable;

This might help. Better way (less memory intensive to QV) is to create the right join in SQL and use the previous script (SQL instead of resident).

Regards,

Kiran Rokkam.

Re: If statement causes QV crash on load

Anonymous — Mon, 02 Jul 2012 16:46:30 GMT

Thank you Kiran, based on your suggestion, I made some changes to my structure. I placed all the fields in the resident load table which included the If statement. Then I renamed the table and dropped the original table. This has provided me with a quick reload and seems to giving me the result I was looking for.

Thank you for your help!