scripting issue

[Anonymous]

Hi

I had  fields like state_new and state_old  . so my  requirement  was

1. showing the distinct vales  combination of both state_new and state_old and want to show as single field

2.if i select either state_old  or state_new i should display old(select field value ) or new(select field value ) with respective their records.

the code  i had done is like this.

Maintable:

LOAD code,

     project,

     state_new,

     state_old,

     daysspent

FROM

(ooxml, embedded labels, table is Sheet1);

main_temp:

load  state_new  as state__temp Resident Maintable;

Concatenate

load  state_old  as state__temp Resident  Maintable;

maintemp2:

load Distinct state__temp as state Resident main_temp;

but i got struct here how to link state field  with  both fields  state_new and  state_old  in maintable . by forming assocation i can acheive  my requirment based on state selection we can get both new and old assocaited records.

can any one help me this please. i had attached the data  please check it if my question not clear

[MK9885]

To combine 2 fields into one use

state_new&' '&state_old as STATE

Ex:

LOAD code,

     project,

     state_new,

     state_old,

     daysspent,

     state_new&' '&state_old as STATE

FROM....

[stigchel]

You need a key to link the maintable and the temp tables. If code and/or project is not a unique key by itself you can use autonumber function and the combination of fields that make a row unique to create one. Load this in both maintable and state table. You don't need the maintemp2 table.

autonumber(expression[ , AutoID])

Returns a unique integer value for each distinct evaluated value of expression encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.

In order to create multiple counter instances if the autonumber function is used on different keys within the script, an optional parameter AutoID can be used for naming each counter.

Examples:

autonumber( Region&Year&Month )

autonumber( Region&Year&Month, 'Ctr1' )

[Anonymous]

HI Khan

i does not want to concatenate  both fields .. i want to union on 2 fields  like table  concatenate in qlikview

[Anonymous]

i does have distinct values. can you make it bit clear

[MK9885]

Maintable:

LOAD code,

     project,

     state_new,

     state_old,

     daysspent

FROM

(ooxml, embedded labels, table is Sheet1);

main_temp:

load

code,

state_new  as state__temp Resident Maintable;

Concatenate

load  state_old  as state__temp Resident  Maintable;

maintemp2:

load

Distinct state__temp as state Resident main_temp;

You'll have link (common field) between Maintable and Main_Temp later you can also drop the table if not needed.

[stigchel]

If code is an unique identifier of a row of data in the main table then:

Maintable:

LOAD code,

     project,

     state_new,

     state_old,

     daysspent

FROM

(ooxml, embedded labels, table is Sheet1);

main_temp:

load  code,state_new  as state Resident Maintable;

Concatenate

load  code,state_old  as state Resident  Maintable;

[Anonymous]

Hi piet.

Thanks for response . but i am  getting duplicate values  because code does not have any unique values. and is their any other way to create serial number using load script.

[MarcoWedel]

Hi,

one solution might be:

tabProjects:
LOAD RecNo() as ID, *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1154450-252403/Book1.xlsx] (ooxml, embedded labels, table is Sheet1);

tabProjStates:
CrossTable (stateType, state)
LOAD ID,
     state_new, 
     state_old
Resident tabProjects;

hope this helps

regards

Marco

[Anonymous]

Hi sir ,

Thanks for your response , but i had a query how did get the  state type and state fields

and how did u done the cross table