3 Replies Latest reply: Jul 8, 2013 10:24 AM by Adriano Fornoni

# Crosstable, Subfield and IntervalMatch

Hi all,

i've a table like this

ID DesRG1RG2
ITEM1Item used 66% timeGr11-01-19000101;2-01-20130615
ITEM2Item used 15% timeGr11-01-19000101
ITEM3Item never usedGr1

I would like to obtain a final table like this:

IDDesRG1MCTreeFromTo
ITEM1Item used 66% timeGr11011900010120130615
ITEM1Item used 66% timeGr120120130615
ITEM2Item used 15% timeGr110119000101

I don't be so good qlikview developer.

I suppose that I've to use before a CrossTable and then a Subfield but i'm not able.

Once I get the table represented, I can use it for an IntervalMatch function.

In this table I've the minimum (From) and maximum (To) value.

Anyone could help me?

Bye and thanks

• ###### Re: Crosstable, Subfield and IntervalMatch

Input:

LOAD ID, Des, RG1, date#(right(subfield(RG2, ';'),8),'YYYYMMDD') as From INLINE [

ID, Des, RG1, RG2

ITEM1, Item used 66% time, Gr1, 1-01-19000101;2-01-20130615

ITEM2, Item used 15% time, Gr1, 1-01-19000101

ITEM3, Item never used, Gr1

];

Result:

ID, Des, RG1, From,

if(previous(ID)=ID, previous(From)) as To

Resident Input

where len(trim(From))>0

order by ID, From desc ;

drop table Input;

See attached example.

• ###### Re: Crosstable, Subfield and IntervalMatch

Now I have to get the two columns MC and Tree but I hope to be able.

Thank you very much

I did't think you could do it in so little time.

Bye

• ###### Re: Crosstable, Subfield and IntervalMatch

Ok, I obtained the desired result.

Thank you very much, I write the example with all the field managed.

Input:

LOAD ID, Des, RG1, subfield(RG2,';') as FIELD1

INLINE [

ID, Des, RG1, RG2

ITEM1, Item used 66% time, Gr1, 1-01-19000101;2-01-20130615

ITEM2, Item used 15% time, Gr1, 1-01-19000101

ITEM3, Item never used, Gr1

];

Result:

ID, Des, RG1,

FIELD1,

if(previous(ID)=ID, previous(FIELD1)) as FIELD2

Resident Input

where len(trim(FIELD1))>0

order by ID, FIELD1 desc ;

drop table Input;

NoConcatenate

, left(FIELD1,1) as MC

, mid(FIELD1,3,2) as Tree

, date#(right(FIELD1,8),'YYYYMMDD') as From

, if(len(Right(FIELD2,8))>0,date(date#(Right(FIELD2,8),'YYYYMMDD')-1,'YYYYMMDD'),'22000101') as To

Resident Result

;

drop table Result;

Bye