Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (2)
1 Solution

Accepted Solutions

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:

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
3 Replies

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:

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

Re: Crosstable, Subfield and IntervalMatch

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

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

    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

Community Browser