topic Re: flags and cross tables with nulls in QlikView

flags and cross tables with nulls

Fri, 17 Oct 2014 08:42:08 GMT

I have successfully created a cross table of 'final sites' but I also need
to include data where the field is null (although I have replaced with text in
resident table) as well as 'y' (I only want to exclude where the field = 'n')

In the resident Main_Table where the field is null I have added text
values that say 'not entered'

I have used the following..

CroTables:

CrossTable (site_code,Flag,1)
LOAD
[Assignment Number],
[YCH G],
[BWM G],
[BRN G],
[LWH G],
[Velindre BRN],
[Clywedog LWH],
[Fan Gorau MCI]
Resident Main_Table;

FinalSites:
NoConcatenate LOAD * Resident
CroTables where Flag =
'y';

DROP table
CroTables;

Re: flags and cross tables with nulls

Gysbert_Wassenaar — Fri, 17 Oct 2014 09:19:30 GMT

Thanks for sharing your story.

Was there any question you wanted to ask as well?

Re: flags and cross tables with nulls

Fri, 17 Oct 2014 09:23:51 GMT

yes its in the first bit above.... my crosstable is pulling through data where the field = 'y' but I also need to bring through null values (where data has not yet been entered) In my resident table I have used isnull function to populate the fields as 'not entered'

I want a flag where field = 'y' or 'not entered'

just not sure how to combine with above script extract?

Re: flags and cross tables with nulls

Gysbert_Wassenaar — Fri, 17 Oct 2014 09:33:15 GMT

I'm afraid I don't understand.

my crosstable is pulling through data where the field = 'y'

Where does it do that? I don't see a where clause doing that in the crosstable load.

In my resident table I have used isnull function to populate the fields as 'not entered'

Which I also don't see in the script you posted. But if you did then you've already solved your problem, haven't you?

I'm sorry, but I just don't understand what you're doing, what you're trying to do, what's not working, what the source data is and what the result data should be. Perhaps these documents can help you:

Re: flags and cross tables with nulls

Fri, 17 Oct 2014 09:42:51 GMT

it follows of from Load script table manipulation?!?

I havent quite got all the data I need....

In the resident table there are 3 values 'y', 'n', 'not entered'

I need the 'y' and 'not entered' to receive a flag using the crosstable function CrossTable (site_code,Flag,1)

do i need to chage the 1 in the script? can i even do it this way

Re: flags and cross tables with nulls

Fri, 17 Oct 2014 10:36:40 GMT

it follows of from Load script table manipulation?!?

I havent quite got all the data I need....

In the resident table there are 3 values 'y', 'n', 'not entered'

I need the 'y' and 'not entered' to receive a flag using the crosstable function CrossTable (site_code,Flag,1)

do i need to chage the 1 in the script? can i even do it this way