Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Script conflicts - exponential computing

Hi all,

I'm having a little trouble solving the following issue. I loading data from 2 separate files. Once the files have been loaded I have to calculate a series of new fields based on the data that has just been loaded. Nothing easier...I thought.

Well, I get to the result somehow but not in the right way I'm guessing. In other words the script does make all the calculations but creates for each step separate tables, each with the extension -x. On the reference manual I read this is done when tables go in conflict.

Well then, I re-designed the script as follows:

[MAIN]:

LOAD [n contract],

     [n Master],

     [carrier] as NUMCIE,

     [Year]

     Polizza,

     mid(Polizza,findoneof(Polizza, '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ\/')) as polizza_short

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[MAIN]:

concatenate LOAD [n contract],

     [n Master],

     [carrier] as NUMCIE,

     [Year]

     Polizza,

     mid(Polizza,findoneof(Polizza, '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ\/')) as polizza_short

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

join LOAD PURGECHAR (NUMCON, '="') AS [n contract],

        PURGECHAR (REGCON, '="') AS [Facility]

FROM

Z:\FCLTY1.CSV

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

join LOAD [n contract], [n Master], Facility, (if(isnull([n Master]), if(not isnull(Facility), Facility), if(isnull(Facility), [n Master], Facility))) as [Desc_Master] resident [MAIN];

join LOAD [Desc_Master],  (if(Desc_Master = '11421086', 'RCO', if(Desc_Master = '11668620', LIAB', **NOTHING**)) as description_master resident [MAIN];

join LOAD [Polizza], if(left([polizza_short], 3)='920', 'Global',   if(left([polizza_short], 3)='609', 'Global', 'PRIV')) as [3_cifre_polizza_short] resident [MAIN];

The script above works, as long as in the two Query Master files I import have just a very limited number of rows. What I noticed is the following: with the first two loads I import some 482 rows. The third load imports another 370 rows. No prob here.

What happens next is that in the last 3 steps (the last 3 join LOAD...) the number of loaded rows is absurd: 849, 1.696, 104.515 respectively.

I can't understand where the mistake is. Seems as if there is a wrong join somewhere that's making row expand exponentially. But where?

Any help will be greatly appreciated. Thanks

Tags (3)
1 Solution

Accepted Solutions

Re: Script conflicts - exponential computing

Your three last LOAD statements are basically statements where you take the "Main" table and join it onto itsef. Which of course will lead to exponential growth of the "Main" table as soon as you have multiple records with the same value.

It will probably work better if you instead use temporary tables. Leave the first three LOAD statements as they are, then replace the last three with:

Main2:

LOAD [n contract], [n Master], Facility, (if(isnull([n Master]), if(not isnull(Facility), Facility), if(isnull(Facility), [n Master], Facility))) as [Desc_Master] resident [MAIN];

Drop table MAIN;

Main3:

LOAD [Desc_Master],  (if(Desc_Master = '11421086', 'RCO', if(Desc_Master = '11668620', LIAB', **NOTHING**)) as description_master resident [Main2];

Drop table Main2;

FinalMain:

LOAD [Polizza], if(left([polizza_short], 3)='920', 'Global',   if(left([polizza_short], 3)='609', 'Global', 'PRIV')) as [3_cifre_polizza_short] resident [Main3];Load

Drop table Main3;

4 Replies
MVP & Luminary
MVP & Luminary

Script conflicts - exponential computing

Hi,

Try using Left/Inner join.

Regards,

Jagan.

Re: Script conflicts - exponential computing

Your three last LOAD statements are basically statements where you take the "Main" table and join it onto itsef. Which of course will lead to exponential growth of the "Main" table as soon as you have multiple records with the same value.

It will probably work better if you instead use temporary tables. Leave the first three LOAD statements as they are, then replace the last three with:

Main2:

LOAD [n contract], [n Master], Facility, (if(isnull([n Master]), if(not isnull(Facility), Facility), if(isnull(Facility), [n Master], Facility))) as [Desc_Master] resident [MAIN];

Drop table MAIN;

Main3:

LOAD [Desc_Master],  (if(Desc_Master = '11421086', 'RCO', if(Desc_Master = '11668620', LIAB', **NOTHING**)) as description_master resident [Main2];

Drop table Main2;

FinalMain:

LOAD [Polizza], if(left([polizza_short], 3)='920', 'Global',   if(left([polizza_short], 3)='609', 'Global', 'PRIV')) as [3_cifre_polizza_short] resident [Main3];Load

Drop table Main3;

MVP & Luminary
MVP & Luminary

Script conflicts - exponential computing

Henric is correct, of course, about the reason for growing volumes. Using Join in those cases is very troublesome. Let me add a slight correction to the suggested code:

1. All 3 transformations can be performed using preceeding loads.

2. Only 2 of the three transofrmations need to be performed sequentially, so we don't have to stack up all three of them.

3. Don't forget to include "*" in order to carry over all other fields that don't require transformation, or you'll lose them on the way. So, the final result could look like this:

...

Main2:

load

     *,

(if(Desc_Master = '11421086', 'RCO', if(Desc_Master = '11668620', LIAB', **NOTHING**)) as description_master,

if(left([polizza_short], 3)='920', 'Global',   if(left([polizza_short], 3)='609', 'Global', 'PRIV')) as [3_cifre_polizza_short]

;

LOAD

     *,

     (if(isnull([n Master]), if(not isnull(Facility), Facility), if(isnull(Facility), [n Master], Facility))) as [Desc_Master]

resident [MAIN];

Drop table MAIN;

rename table Main2 to MAIN;

Script conflicts - exponential computing

Oleg's solution is better than mine. Don't know why I didn't think of that one... Both will work though.

HIC