topic Re: If - Then - ElseIf - EndIf in Script in QlikView

If - Then - ElseIf - EndIf in Script

johnca — Mon, 16 Oct 2017 22:31:57 GMT

I'm trying to figure out if this is even possible. I haven't had any luck on my own using the following routine and any similar. I'm trying to apply logic on a resident table and make field values based on the initial logic. I realize the following is probably not syntactically correct. Assume the variables are valued values.

Anything like this possible without loading the resident table 3x to get the desired result?

Summary_Data:

NoConcatenate

LOAD

Country = 'US'

and Date >= '$(vStartOneDate)'

and Date <= '$(vTableOneDate)'

Then

Product,

App,

Country,

'One' as Source_Table

Else If

Country = 'US'

and Date >= '$(vStartTwoDate)'

and Date <= '$(vTableTwoDate)'

Then

Product,

App,

Country,

'Two' as Source_Table

Else If

Country = 'US'

and Date >= '$(vStartThreeDate)'

and Date <= '$(vTableThreeDate)'

Then

Product,

App,

Country,

'Three' as Source_Table

End If

Resident Detailed_Data;

Re: If - Then - ElseIf - EndIf in Script

Anil_Babu_Samineni — Tue, 17 Oct 2017 01:04:04 GMT

I believe no, You have to load 3 times for that

Re: If - Then - ElseIf - EndIf in Script

Tue, 17 Oct 2017 10:53:18 GMT

Hi,

Try below.

Country = 'US'

and Date >= '$(vStartOneDate)'

and Date <= '$(vTableOneDate)'

Then

LOAD Product,

App,

Country,

'One' as Source_Table

Resident Detailed_Data;

Else If

Country = 'US'

and Date >= '$(vStartTwoDate)'

and Date <= '$(vTableTwoDate)'

Then Load

Product,

App,

Country,

'Two' as Source_Table

Resident Detailed_Data;

Else If

Country = 'US'

and Date >= '$(vStartThreeDate)'

and Date <= '$(vTableThreeDate)'

Then Load

Product,

App,

Country,

'Three' as Source_Table

Resident Detailed_Data;

End If

Thanks!

Re: If - Then - ElseIf - EndIf in Script

madhumitha — Tue, 17 Oct 2017 11:19:01 GMT

Hi John,

Since you are updating the Source_Table field with three different values, you have to load the resident table thrice while concatenating all three.

Thanks!

Re: If - Then - ElseIf - EndIf in Script

sunny_talwar — Tue, 17 Oct 2017 11:37:01 GMT

May be this

Summary_Data:

NoConcatenate

LOAD

Product,

App,

Country,

If(Country = 'US' and Date >= '$(vStartOneDate)' and Date <= '$(vTableOneDate)', 'One',

If(Country = 'US' and Date >= '$(vStartTwoDate)' and Date <= '$(vTableTwoDate)', 'Two',

If(Country = 'US' and Date >= '$(vStartThreeDate)' and Date <= '$(vTableThreeDate)', 'Three'))) as Source_Table

Resident Detailed_Data;

Re: If - Then - ElseIf - EndIf in Script

johnca — Tue, 17 Oct 2017 15:43:13 GMT

I think I mis-wrote my sample code. I also needed to add counts of things based on the date ranges, so would need to add a group by. The entire effort was to try and speed up the load by reading the resident table once. Based on my initial sample code I believe Sunny had the right idea, but with the embedded if statements on several fields probably slows it down. Will try any way. The actual resident table has about 30 days of ~500,000 rows, or 15M rows. It now takes 18 minutes per load for each (3) type so i all about 55 minutes.

Re: If - Then - ElseIf - EndIf in Script

sunny_talwar — Tue, 17 Oct 2017 23:32:53 GMT

Can you show the script you are running right now?

Re: If - Then - ElseIf - EndIf in Script

johnca — Wed, 18 Oct 2017 18:31:38 GMT

Hi Sunny, I think I can show it in it's raw form. Nothing confidential. Each residential load (3x) take 15-18 minutes) and the second 2 concatenate to the first. THANKS for taking a look...John

//US NMC data

TRACE Generating US NMC summary data...;

Summary_Data:

NoConcatenate

LOAD

'$(vUS_NMC_TableDate)' as MS_Date,

'$(vUS_NMC_SummaryType)' as MS_Summary_Type,

UA as MS_UA,

App as MS_App,

Country as MS_Country,

Product as MS_Product,

NMC_Acct_Num as MS_NMC_Acct_Num,

Msg_Type as MS_Msg_Type,

Transport as MS_Transport,

Count(distinct UA) as MS_Units,

Sum(Transactions) as MS_Transactions,

Sum(Bytes) as MS_Bytes,

Count(distinct if(SAT_Trans > 0, UA)) as MS_SAT_Units,

Sum(SAT_Trans) as MS_SAT_Trans,

Sum(SAT_Bytes) as MS_SAT_Bytes,

Count(distinct if(OVT_Trans > 0, UA)) as MS_OVT_Units,

Sum(OVT_Trans) as MS_OVT_Trans,

Sum(OVT_Bytes) as MS_OVT_Bytes,

Count(distinct if(WIFI_Trans > 0, UA)) as MS_WIFI_Units,

Sum(WIFI_Trans) as MS_WIFI_Trans,

Sum(WIFI_Bytes) as MS_WIFI_Bytes

Resident Detailed_Data

Where Country = 'US'

and Date >= '$(vUS_NMC_SummaryStartDate)'

and Date <= '$(vUS_NMC_TableDate)'

//NMC App values are numeric

and IsNum(App)

Group by UA, App, Country, Product, NMC_Acct_Num, Msg_Type, Transport;

//US NGB data

TRACE Generating US NGB summary data...;

NGB_Summary_Data:

Concatenate (Summary_Data)

LOAD

'$(vUS_NGB_TableDate)' as MS_Date,

'$(vUS_NGB_SummaryType)' as MS_Summary_Type,

UA as MS_UA,

App as MS_App,

Country as MS_Country,

Product as MS_Product,

NMC_Acct_Num as MS_NMC_Acct_Num,

Msg_Type as MS_Msg_Type,

Transport as MS_Transport,

Count(distinct UA) as MS_Units,

Sum(Transactions) as MS_Transactions,

Sum(Bytes) as MS_Bytes,

Count(distinct if(SAT_Trans > 0, UA)) as MS_SAT_Units,

Sum(SAT_Trans) as MS_SAT_Trans,

Sum(SAT_Bytes) as MS_SAT_Bytes,

Count(distinct if(OVT_Trans > 0, UA)) as MS_OVT_Units,

Sum(OVT_Trans) as MS_OVT_Trans,

Sum(OVT_Bytes) as MS_OVT_Bytes,

Count(distinct if(WIFI_Trans > 0, UA)) as MS_WIFI_Units,

Sum(WIFI_Trans) as MS_WIFI_Trans,

Sum(WIFI_Bytes) as MS_WIFI_Bytes

Resident Detailed_Data

Where Country = 'US'

and Date >= '$(vUS_NGB_SummaryStartDate)'

and Date <= '$(vUS_NGB_TableDate)'

//NMC App values are text

and IsText(App)

Group by UA, App, Country, Product, NMC_Acct_Num, Msg_Type, Transport;

//CA data

TRACE Generating CA summary data...;

CA_Summary_Data:

Concatenate (Summary_Data)

LOAD

'$(vCATableDate)' as MS_Date,

'$(vCA_SummaryType)' as MS_Summary_Type,

UA as MS_UA,

App as MS_App,

Country as MS_Country,

Product as MS_Product,

NMC_Acct_Num as MS_NMC_Acct_Num,

Msg_Type as MS_Msg_Type,

Transport as MS_Transport,

Count(distinct UA) as MS_Units,

Sum(Transactions) as MS_Transactions,

Sum(Bytes) as MS_Bytes,

Count(distinct if(SAT_Trans > 0, UA)) as MS_SAT_Units,

Sum(SAT_Trans) as MS_SAT_Trans,

Sum(SAT_Bytes) as MS_SAT_Bytes,

Count(distinct if(OVT_Trans > 0, UA)) as MS_OVT_Units,

Sum(OVT_Trans) as MS_OVT_Trans,

Sum(OVT_Bytes) as MS_OVT_Bytes,

Count(distinct if(WIFI_Trans > 0, UA)) as MS_WIFI_Units,

Sum(WIFI_Trans) as MS_WIFI_Trans,

Sum(WIFI_Bytes) as MS_WIFI_Bytes

Resident Detailed_Data

Where Country = 'CA'

and Date >= '$(vCA_SummaryStartDate)'

and Date <= '$(vCATableDate)'

Group by UA, App, Country, Product, NMC_Acct_Num, Msg_Type, Transport;

Re: If - Then - ElseIf - EndIf in Script

sunny_talwar — Sat, 21 Oct 2017 11:17:49 GMT

Looking at this I have two things to say

1) I am not surprised that the load times are high. Aggregations in the script can take a lot of toll and depending on the number or rows you are aggregating on, it can last hours.

2) So, are you looking to load this just one time instead of loading the same table thrice? It is def. possible, but you will have to test if you see any performance gains or not because earlier you were breaking down the aggregations into three parts (lets say 100k, 100k, 100k) whereas the combined will do the same aggregation on 300k rows. Where are you creating your Source_Table in the above script? I did not see that?

Re: If - Then - ElseIf - EndIf in Script

uroboros — Sat, 21 Oct 2017 16:38:42 GMT

This IF structure will not work, you must use the alternative that is used in graphics, IF (<Condition>, True, false)

Re: If - Then - ElseIf - EndIf in Script

johnca — Sun, 22 Oct 2017 18:26:21 GMT

Thanks Sunny, that's kind of what we figured, but still wanted another set of eyes. Now 15 minutes per run or 45 total. I have seen longer as you've mentioned. It's still faster than having it in the users' front end.

I'm not certain of the number of rows in the source table (this is a contemporary's project) but I believe it is in the 10's of million of rows. The source table is created reading a database on a daily basis.

I (and my cohort) appreciate the thought provided.

John

Re: If - Then - ElseIf - EndIf in Script

Peter_Cammaert — Mon, 23 Oct 2017 07:32:18 GMT

You could start by trying to remove the IF functions from the Count() aggregation functions. Since you're reading everything from an internal table, it would be better to create field copies that have their value set to NULL if the condition xxx<=0 is fulfilled during the initial LOAD.

Another thought: set one of three flags to 0/1 if the condition is fulfilled, and use Sum() instead of Count() to create the counts. Sum() is way faster than Count(). However the DISTINCT keyword may force you to create a complex version of this simple expression.