Transpose a table with 2 filters

Jorghino75 — Fri, 05 Apr 2024 08:06:53 GMT

Hi all

I successfully worked on a transposed table using this link

https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/ thanks to your help.

I have now another problem since I need to add another filter. I tried mapping the same field using again the ApplyMap function before running the generic load function.

Basically, I followed these steps

Load filter 1
Load filter 2
Load source file
Map source file (resident) with filter 1
Map source file (resident) with filter 2
Sum the hours
Use the generic load & then the routing to put all the tables in one.

However, this doesn't seem to be working.as I'm not getting all the record combination (ID+Date)

I'm looking to have a model like this table where the total of Hours gets calculated for each filter 1 and each filter 2 field.

I'm attaching a sample file with data.

Any guidance would be greatly appreciated.

Re: Transpose a table with 2 filters

edwin — Fri, 12 Apr 2024 19:45:19 GMT

youre not articluating what the business rules are, for filter2 there are some cases where there are multiple matches. is there a relationship between the 1st and 2nd filter, for example this one:

for the first line, Num had a match - ALOB, but 2nd didnt have a match (MB) so it appears to NOT be a valid line item. however, 2nd line has a match for Num but multiple matches for Code - does that mean if 1st and 2nd filter dont result in a match dont use, if 2nd filter has multiple matches use 1st match, if 2nd filter has only 1 match use 2nd filter? what are the bsuiness rules?

topic Re: Transpose a table with 2 filters in App Development

Transpose a table with 2 filters

Re: Transpose a table with 2 filters