topic Re: Cross over table with Flags in QlikView

Cross over table with Flags

paullawrence190 — Wed, 25 Nov 2020 16:16:04 GMT

I want to take some data in a cross over table and put some flags on it. The flags work fine when not crossover but fail when I convert to crossover. Is there any ay to make this work please. Data is coming form a spread sheet that is fed out of Sage so I cant alter it easily.

Thanks

Paul

Simple table:

LOAD
Client,
Code,
Jan,
Feb,
Mar,
Apr,
May,
June,
if(WildMatch([Code],'*1001*') and WildMatch([Code],'*Revenue*'),1,0) as MSKTriageFLag

Cross:

CrossTable(Date, Data, 2)
LOAD Client,
Code,
Jan,
Feb,
Mar,
Apr,
May,
June,

if(WildMatch([Code],'*1001*') and WildMatch(

,'*Revenue*'),1,0) as MSKTriageFLag

Re: Cross over table with Flags

Frank_Hartmann — Fri, 29 Jul 2016 16:22:14 GMT

try like this:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

LOAD Client,

Code,

Jan,

Feb,

Mar,

Apr,

May,

June

FROM

[227208.xlsx]

(ooxml, embedded labels, table is Tabelle1);

CrossTable(Date, Data, 2)

LOAD Client,

Code,

Jan,

Feb,

Mar,

Apr,

May,

June

FROM

[227208.xlsx]

(ooxml, embedded labels, table is Tabelle1);

Load *,

if(WildMatch(Code,'*1001*'),1,if( WildMatch(Code,'*Revenue*'),1,0)) as MSKTriageFLag

Resident 2;

DROP Table 2,1;

Re: Cross over table with Flags

MarcoWedel — Fri, 29 Jul 2016 22:08:25 GMT

maybe like

CrossTable(Date, Data, 3)
LOAD Client,
Code,

if(WildMatch([Code],'*1001*') and WildMatch(

,'*Revenue*'),1,0) as MSKTriageFLag,

Jan,
Feb,
Mar,
Apr,
May,
June

hope this helps

regards

Marco

Re: Cross over table with Flags

vinieme12 — Sat, 30 Jul 2016 08:35:19 GMT

Do a cross tab of your resident simple table

TAB_SIMPLE:

LOAD
Client,
Code,
Jan,
Feb,
Mar,
Apr,
May,
June,
if(WildMatch([Code],'*1001*') and WildMatch(

,'*Revenue*'),1,0) as MSKTriageFLag

Cross:

CrossTable(Date, Data, 3)
LOAD Client,
Code,

MSKTriageFLag,

Jan,
Feb,
Mar,
Apr,
May,
June

RESIDENT TAB_SIMPLE;

Drop Table TAB_SIMPLE;

Re: Cross over table with Flags

paullawrence190 — Mon, 01 Aug 2016 07:39:33 GMT

Thanks this is perfect I thought it was going to need a Resident table but was not sure how to do it.

Re: Cross over table with Flags

oknotsen — Mon, 01 Aug 2016 07:46:29 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .