Load script table manipulation?!?

[#1]

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?

[tresesco · Accepted solution]

try like:

CroTables:

Crosstable (site_code, Flag,1)

Load

          *

from <>;

NoConcatenate

Final:

Load

          *

Resident CroTable Where Flag='y';

Drop table CroTable;

[qlikconsultant]

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 <>;

[#4]

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:(

[#5]

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...

[tresesco]

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],
          ....

[qlikconsultant]

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

[#8]

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

[#9]

FinalSites:

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

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

[#10]

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