Tree view for account level of detail

[#1]

Hi all,

I want the end-user to determine the level of (account) detail they want to see. Please find attached an Excel file with a sheet input and output. How can I go from Input to Output using QlikView script? Of course this is a limited data set, in reality there are much more records...

Input: I have a rownumber, Account level of detail (3 - 7), object account en subsidiary (data from JD Edwards)

Output: level 7 is Object Account & Subsidiary, level 6 is Object Account... level 5 is more difficult as this requires to look up and find the first Object Account where Account Level of Detail is 5. Level 4 is then the Object Account where Account Level of Detail is 4, etc.

Thanks for your help!

Regards,

Marcel

[rubenmarin · Accepted solution]

Hi Marcel, following script may help you:

INPUT:

LOAD RowNr,

     [Account Level of Detail],

     [Object Account],

     Subsidiary

FROM

[.\LevelOfAccount_QV.xlsx]

(ooxml, embedded labels, table is INPUT);

Temp:

LOAD If([Account Level of Detail]>=3 and IsNull(Level3), Peek(Level3), Level3) as Level3,

    If([Account Level of Detail]>=4 and IsNull(Level4), Peek(Level4), Level4) as Level4,

    If([Account Level of Detail]>=5 and IsNull(Level5), Peek(Level5), Level5) as Level5,

    If([Account Level of Detail]>=6 and IsNull(Level6), Peek(Level6), Level6) as Level6,

    If([Account Level of Detail]=7 and IsNull(Level7), Peek(Level7), Level7) as Level7;

LOAD [Account Level of Detail],

    If([Account Level of Detail]=3, [Object Account]) as Level3,

    If([Account Level of Detail]=4, [Object Account]) as Level4,

    If([Account Level of Detail]=5, [Object Account]) as Level5,

    If([Account Level of Detail]=6, [Object Account]) as Level6,

    If([Account Level of Detail]=7, Subsidiary) as Level7

Resident INPUT;

OUTPUT:

NoConcatenate LOAD * Resident Temp Where not IsNull(Level7);

DROP Tables Temp, INPUT;

[#3]

Well that was embarrassingly simple!

Thank you for your help!!!