Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

Please I'm in difficulty.

Bye and thanks

Adriano

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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