Crosstable, Subfield and IntervalMatch

[#1]

Hi all,

i've a table like this

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

I would like to obtain a final table like this:

ID	Des	RG1	MC	Tree	From	To
ITEM1	Item used 66% time	Gr1	1	01	19000101	20130615
ITEM1	Item used 66% time	Gr1	2	01	20130615
ITEM2	Item used 15% time	Gr1	1	01	19000101

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?

Please I'm in difficulty.

Bye and thanks

Adriano

[Gysbert_Wassenaar · Accepted solution]

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:

NoConcatenate load

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.

[#3]

Thanks, the answer is correct.

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

Adriano

[#4]

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

    ITEM4, ADRIANO, GR_AF, 5-01-19000101;2-01-20130701;3-03-20140115

    ITEM5, ADRY, GR_AA, 1-02-19000101;2-02-20130701;3-02-20140115;4-02-20140320

    ITEM6, ADRY, GR_AA, 4-01-19000101;3-02-20130701;2-03-20140115;1-04-20140515

    ITEM7, ADRY_X, GR_AX, 1-01-19000101;3-02-20130701;2-03-20140115;5-04-20140620

];

Result:

NoConcatenate load

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;

ResultAdriano:

NoConcatenate

LOAD ID, Des, RG1

, 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

Adriano