cross tab issue with null...

Hi, Thanks for your time. I have this scenario where the data is input from JSON file.. when i try to cross tab the data together it is not aligning correct

what is this data about? products and the list of applications that belong to that product..

first dataset is for products and then applications and some product attributes

when I try to cross tab.. since there is null in one of column that iam using for group by in cross tab -- it is going to a separate record - JSON_Name4 has null because these are for product only

after the cross tab it looks like below

I have to do with 1 single cross tab so that i have the workflow is not complex... any suggestions or ideas

Data Investigation

Accepted answers

All comments

BrandonB

This is because your JSON Name 5 is null resulting in a null header when the first 5 rows are being crosstabbed. I would recommend either adding a filter before the crosstab and saying !IsNull(JSON_Name5), or set the filter for JSON_Name3="applications" to get rid of these rows in your final table.

Raj_007

Hi Brandon, I need those records because they represent my products... so after the cross tab I took the summary tool and grouped by all those ...now i need to use the multi row formula ,....but lost in how to use this

JSON_Name2 is never null and it gets incremented with the next value..when the JSON_Name4 value is NULL and _Null_ has the value -- we take this value and put it in all rows of the below it

so what does this say.. this is my product that is in _Null_ where JSON_Name4 is NULL ... so i need to take this and put it in all the rows where it is null

when the JSON_Name2 becomes 1 there will be NULL in JSON_Name4 and _Null_ will have another product name ..... we take this and fill the rows in _Null_

Thanks a lot

Raj_007

Hi Brandon, may be this screenshot will give an idea of the reason why i want to use the multi row formula so that i can put or keep the product related info below the rows

so that i get the product related information for each application

BrandonB

In a multi row formula

IF IsEmpty([lineOfBusinessName])

THEN [Row-1:lineOfBusinessName]

ELSE [lineOfBusinessName]

ENDIF

Raj_007

Hi Brandon, I tried it but it is not working for all the rows - looks like i might be missing something

BrandonB

Ah, that is because you need to set the multi row formula to Update Existing Field rather than Create New Field. Then you will want to choose the lineOfBusinessName field and it will populate accordingly.

Raj_007

Thank you Brandon, so for testing i was using only 1 product say if i get 100 more products my JSON_Name2 values will be 0 to 99 - and JSON_Name4 will have 1 null and may include like 0 1 2 3 or so depending on how many apps inside it... this logic would still work right? may be i might need to look at the group by in multi row formula tool -

Brandon, Thank you so much . Really appreciate your time

BrandonB

Without knowing what the structure looks like for multiple products it’s tough to say for sure but I’d say it is most likely just doing a group by JSON_Name2 in the multi row formula

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703