Populate Table Dinamically

[marcel_olmo]

Hi guys,

I have a table like this :

A list of possible SubGroups like this :

And I'd like to populate the blank fields of the Subgroup Column with the possible Subgroup values not populated before (blank means all possible values except the ones that were previously populated). This is my desired result :

How can I do it with Qlik? Anyone can give me some help with that?

I've attached an example here.

Regards, Marcel.

[maxgro · Accepted solution]

Tmp:

load distinct Year, Month, Group, Reference

FROM Example.xlsx (ooxml, embedded labels, table is Hoja1);

join(Tmp)

LOAD SubGroup

FROM Example.xlsx (ooxml, embedded labels, table is Hoja2);

left join (Tmp)

load Year, Month, Group, Reference, SubGroup, Amount

FROM Example.xlsx (ooxml, embedded labels, table is Hoja1);

left join (Tmp)

load Year, Month, Group, Reference, Amount as Amount2

FROM Example.xlsx (ooxml, embedded labels, table is Hoja1)

where len(trim(SubGroup))=0;

Final:

load Year, Month, Group, SubGroup, Reference, alt(Amount, Amount2) as Amount

Resident Tmp;

DROP Table Tmp;

[marcel_olmo]

Thanks Maxgro! It works like a charm.

Regards, Marcel.