topic Re: scripting issue in QlikView

scripting issue

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

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

Re: scripting issue

MK9885 — Fri, 04 Nov 2016 13:24:44 GMT

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

Re: scripting issue

stigchel — Fri, 04 Nov 2016 13:28:04 GMT

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' )

Re: scripting issue

Anonymous — Fri, 04 Nov 2016 13:32:20 GMT

HI Khan

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

Re: scripting issue

Anonymous — Fri, 04 Nov 2016 13:33:24 GMT

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

Re: scripting issue

MK9885 — Fri, 04 Nov 2016 13:37:19 GMT

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.

Re: scripting issue

stigchel — Fri, 04 Nov 2016 14:16:55 GMT

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;

Re: scripting issue

Anonymous — Fri, 04 Nov 2016 16:25:03 GMT

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.

Re: scripting issue

MarcoWedel — Sat, 05 Nov 2016 00:17:14 GMT

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

Re: scripting issue

Anonymous — Mon, 07 Nov 2016 03:04:13 GMT

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

Re: scripting issue

MarcoWedel — Mon, 07 Nov 2016 20:58:10 GMT

Hi,

the stateType and state fields are created by the CrossTable load as described here:

Crosstable ‒ QlikView

hope this helps

regards

Marco