topic Re: Load script table manipulation?!? in QlikView

Load script table manipulation?!?

Thu, 16 Oct 2014 12:01:02 GMT

hoping someone can help...if this is even possible?!?

I have the following table...

number	YCH G	BWM G	BRN G	LWH G	KNI G	LNI G	MAC G	MCI G	VWM G
10602906-2	y	n	n	n	n	n	n	n	n
10605441	n	y	n	n	n	n	n	n	n
20118159	n	n	n	n	n	n	n	y	y
20743581	n	y	y	n	n	n	n	n	n

and what I want to do is create a secondary table as a lookup for my main table above.. i.e when I select number 20118159 I want to be able to select from a list any site code where there is a 'y' in the column... can I create a create a table like this...

number	site_code
10602906-2	YCH G
10605441	BWM G
20118159	MCI G
20118159	VWM G
20743581	BWM G
20743581	BRN G

so that I can have a list box of sites and my main table will reurn the related numbers?!?

Does that make any sense?

Re: Load script table manipulation?!?

tresB — Thu, 16 Oct 2014 12:06:09 GMT

try like:

CroTables:

Crosstable (site_code, Flag,1)

Load

from <>;

NoConcatenate

Final:

Load

Resident CroTable Where Flag='y';

Drop table CroTable;

Re: Load script table manipulation?!?

qlikconsultant — Thu, 16 Oct 2014 12:16:27 GMT

You could also use ApplyMap and maping load

CroTables:

mapping load *;

Load

number, site_code from <>;

Final:

Load

ApplyMap ('CroTables', number) as site_code

from <>;

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 12:26:27 GMT

have tried this...

CroTables:
CrossTable (site_code,Flag,1)
LOAD

   [YCH G],

   [BWM G],

   [BRN G],

   [LWH G],

   [KNI G],

   [LNI G],

   [MAC G],

   [MCI G],

   [VWM G],

   [YCH CT],

   [BWM CT],

   [Crick CT],

   [Hay/ Talgarth CT],

   [Builth CT],

   [LWH CT],

   [KNI CT],

   [Llanfyllin CT],

   [Llanfair C CT],

   [VWM CT],

   [MCI CT],

   [Mont CT],

   [MAC CT],

   [Llani CT],

   [Tawe YCH],

   [Crug BWM],

   [Velindre BRN],

   [Clywedog LWH],

   [Fan Gorau MCI]


FROM

(ooxml, embedded labels, table is [Current-Open
List]);

FinalSites:

NoConcatenate LOAD * Resident CroTables where Flag = 'y';

DROP table CroTables;

but even were there is 'n' against a number it is bringing back a site code:(

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 12:28:08 GMT

not sure how I would get my mapping table from the base table I have . Can't change the base table as it feeds direct from another system...

Re: Load script table manipulation?!?

tresB — Thu, 16 Oct 2014 12:38:17 GMT

Number field has to be the first field in your load statement like:

CroTables:
CrossTable (site_code,Flag,1)
LOAD

    NumberField,//This would be left unchanged as the to be qualified number of fields is set 1

   [YCH G],

   [BWM G],
          ....

Re: Load script table manipulation?!?

qlikconsultant — Thu, 16 Oct 2014 12:40:20 GMT

How (excel, database, txt, ..) is the number and side_code Attribute stored?

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 12:40:31 GMT

just noticed that and re-did ... all working now

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 12:48:37 GMT

FinalSites:

NoConcatenate LOAD * Resident CroTables where Flag = 'y';

where can i put isnull in this bit? no idea of the syntax

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 12:50:08 GMT

excel but number is in column and site_code is in row (in header column)

Re: Load script table manipulation?!?

qlikconsultant — Thu, 16 Oct 2014 12:53:54 GMT

I'm not sure what you you want with isnull?

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 12:57:26 GMT

when there is a 'y' or 'null' in the filed I want it flagged (i.e treating nulls as 'y')

Re: Load script table manipulation?!?

qlikconsultant — Thu, 16 Oct 2014 13:04:48 GMT

Should look like

NoConcatenate LOAD * Resident CroTables where Flag = 'y' or IsNull(Flag)='-1'

Re: Load script table manipulation?!?

Thu, 16 Oct 2014 13:18:12 GMT

added that all i think i need to do now is add to the site_code lookup table (where description is held) is where site_code isnull then description is 'not entered' this lookup is coming from an excel sheet.

Re: Load script table manipulation?!?

Tue, 21 Oct 2014 07:28:16 GMT

Thanks so much for you help the other day.... I also need to pick up null values as well as 'y' , how can I include these?

Re: Load script table manipulation?!?

tresB — Tue, 21 Oct 2014 07:36:10 GMT

Try like:

Where Flag='y' or Len(Trim(Flag))=0 ;

Re: Load script table manipulation?!?

Tue, 21 Oct 2014 07:49:33 GMT

that doesnt seem to work

Is there any way I can get the 'nulls' to text as 'not entered' and display these in my location table?

Re: Load script table manipulation?!?

tresB — Tue, 21 Oct 2014 08:06:01 GMT

Try like:

NullAsValue YourField;

Set NullValue = 'Not entered';

Re: Load script table manipulation?!?

Tue, 21 Oct 2014 08:19:06 GMT

sorry not sure where to put this

Re: Load script table manipulation?!?

tresB — Tue, 21 Oct 2014 08:26:47 GMT

In the script before you start your scripting: