topic Re: Reloading data as cross table - script failure in QlikView

Reloading data as cross table - script failure

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm getting the 'Execution of script failed' message (but no explanation why).

It's happened since I added in a section to reload the same datasource, but as a cross table.

Is anyone able to see where I'm going wrong? I don't think I have any loops in there...

TABLE_DATA:
LOAD 
     '$(vProgress1)' as Progress_point,
     ID,
     ApplyMap('Long_map',Type) as Type_long,
     Set as Initial_Set,
     Current,
    Expected
    FROM
[..\Data received\$(vProgress1)\$(vFilename1).xlsx]
(ooxml, embedded labels, table is [Data template]);


temp1:
CrossTable(Cat, Outcome, 10)
LOAD 
 '$(vProgress1)' as Progress_point,
     ID, 
     ApplyMap('Long_map',Type) as Type_long,
     Set as Initial_Set,
     Current, 
     Expected
FROM
[..\Data received\$(vProgress1)\$(vFilename1).xlsx]
(ooxml, embedded labels, table is [Data template]);


LEFT JOIN (TABLE_DATA) LOAD *
Resident temp1;
Drop TABLE temp1;
LEFT JOIN (TABLE_DATA) LOAD *
Resident temp1;
Drop TABLE temp1;
LE temp1;LE

It it helps, what I wanted from the original 'TABLE_DATA' is to keep 'Current' and 'Expected' as they are. But then also add two new fields, one called 'Outcome' which would hold the values of Current and Expected, and and another called 'Cat' which would tell me whether the Outcome was from the Current or Expected field.

Re: Reloading data as cross table - script failure

vishsaggi — Thu, 13 Jul 2017 14:59:03 GMT

What happens if you just run these two? Picked from the script above. Include your Mapping load too.

TABLE_DATA:

LOAD '$(vProgress1)' as Progress_point,

ID,

ApplyMap('Long_map',Type) as Type_long,

Set as Initial_Set,

Current,

Expected

FROM [..\Data received\$(vProgress1)\$(vFilename1).xlsx] (ooxml, embedded labels, table is [Data template]);

temp1:

CrossTable(Cat, Outcome, 10)

LOAD '$(vProgress1)' as Progress_point,

ID,

ApplyMap('Long_map',Type) as Type_long,

Set as Initial_Set,

Current,

Expected

FROM [..\Data received\$(vProgress1)\$(vFilename1).xlsx] (ooxml, embedded labels, table is [Data template]);

Re: Reloading data as cross table - script failure

jessica_webb — Fri, 14 Jul 2017 06:27:50 GMT

I'm getting the 'Execution of script failed' message (but no explanation why).

Re: Reloading data as cross table - script failure

jmvilaplanap — Fri, 14 Jul 2017 08:40:48 GMT

Maybe you are using a reseved word "SET" in the data fields.

Re: Reloading data as cross table - script failure

jmvilaplanap — Fri, 14 Jul 2017 08:44:18 GMT

Another thing I saw is that in the cross table you have a 10 in the config (first line) and you only have 6 fields.

Re: Reloading data as cross table - script failure

jessica_webb — Fri, 14 Jul 2017 11:37:16 GMT

The original file itself has 10 fields, so that wouldn't be an issue. And the word 'Set' in a data field also shouldn't matter...

But thanks for the suggestions

Re: Reloading data as cross table - script failure

jmvilaplanap — Fri, 14 Jul 2017 12:00:37 GMT

Maybe the file is used by another program, I had problems with this, but trying to save a QVD

Re: Reloading data as cross table - script failure

sunny_talwar — Fri, 14 Jul 2017 12:08:22 GMT

So you are loading the same data set twice one where you crosstable and one where you don't? what's the use?

Re: Reloading data as cross table - script failure

jessica_webb — Fri, 14 Jul 2017 12:10:18 GMT

Nope, not being used anywhere else!

Re: Reloading data as cross table - script failure

jessica_webb — Fri, 14 Jul 2017 12:11:29 GMT

Hi Sunny,

Yes, same data set being loaded twice.

The reason is, what I wanted from the original 'TABLE_DATA' is to keep 'Current' and 'Expected' as they are (i.e. two distinct fields).

But then I also want to add two new fields, one called 'Outcome' which would hold the values of Current and Expected, and and another called 'Cat' which would tell me whether the Outcome was from the Current or Expected field.

Re: Reloading data as cross table - script failure

rupamjyotidas — Fri, 14 Jul 2017 19:23:23 GMT

Do this first : Save in QVD

temp1:

CrossTable(Cat, Outcome, 10)

LOAD '$(vProgress1)' as Progress_point,

ID,

ApplyMap('Long_map',Type) as Type_long,

Set as Initial_Set,

Current,

Expected

FROM [..\Data received\$(vProgress1)\$(vFilename1).xlsx] (ooxml, embedded labels, table is [Data template]);

Then Second: Save in QVD

Then use those QVD with the Original Table

Re: Reloading data as cross table - script failure

jessica_webb — Mon, 17 Jul 2017 07:39:55 GMT

My apologies Jose! You were absolutely right, it was the '10' qualifier fields that was the issue. Strange, as in a previous crosstable I refer to the full number of fields in the original datasource and it works ok (even though I don't load all of them). But in this case, changing the qualifier to '4' has fixed it!

Thanks, and apologies again

Re: Reloading data as cross table - script failure

jmvilaplanap — Mon, 17 Jul 2017 08:17:00 GMT

You're welcome Jessica

For the cross tables I always use the wizard because I never remember the parameters.

Regards.