topic Re: Select different fields depending on other field values in QlikView

Select different fields depending on other field values

lanlizgu — Tue, 12 Jun 2018 12:27:19 GMT

In the script, for a field I would like to use the data from one field or another depending of the value of this field.

For example,

If the value inside the Item field is 1, I would like to use the column StartDate, and if the value inside the Item field is 2, I would like to use the data from EndDate column

Item	Date
1	StartDate
2	EndDate

Obviously this should be automated and continue working in case of adding another item. I don't want to do this by adding the literal.

Thank you.

Re: Select different fields depending on other field values

bramkn — Tue, 12 Jun 2018 12:39:04 GMT

I think you can create a mappingtable/applymap for this and put the outcome between [] to use the outcome as field name. Not sure, so you will have to try and see if it works.

Re: Select different fields depending on other field values

lanlizgu — Tue, 12 Jun 2018 14:09:02 GMT

Not working an applymap here. I have created the following Applymap

XXXXXX:

Mapping LOAD

Item,

Date

FROM [xxxx.xlsx]

(ooxml, embedded labels, table is xxxx);

and later the followinf line for the Applymap

applymap('XXXXXX', [Item], null()) as [Date]

but what I get for Item 1 is the descriptor StartDate and not the value of the column StartDate

Re: Select different fields depending on other field values

lanlizgu — Tue, 12 Jun 2018 14:18:39 GMT

with and if it would be something like that

if(Item='1', StartDate, if(Item='2', EndDate)) as Date

the thing is that I would like to have this in an automatic way and not add everytime there is a new Item a new line to the if

Re: Select different fields depending on other field values

Anonymous — Tue, 12 Jun 2018 14:42:52 GMT

Hello,

Try something like this:

Fields:

Mapping

Load * Inline

[

Item, Date

1, StartDate

2, EndDate

];

Data:

Load * Inline

[Item, StartDate, EndDate

1, 11/06/2018, 12/06/2018

2, 09/06/2018, 10/06/2018

1, 07/06/2018, 08/06/2018

2, 06/06/2018, 07/06/2018

];

FinalDate:

Load *,

Peek(applymap('Fields', [Item], null()), RowNo() - 1, 'Data') as [Date]

Resident Data;

Drop Table Data;

Result:

Re: Select different fields depending on other field values

lanlizgu — Tue, 12 Jun 2018 15:07:18 GMT

Perfectly working.

Thank you so much Daniel!!