Cross table with four attributes

[Anonymous]

Hi

I need to normalise a  table and have read a number of the communities posts but they are not quite the same as my data table.

I know that I must do multiple steps but I am stuck after the initial cross table script. I lose dimensions and then cannot get to the next steps.

Once I have the fields loaded I then need to do some transformations using if statements that I have already compiled and loop through the other files in the directory to consume these as well.

Attached is my data file, how it should look (example from and to) and my attempt at the Load data manually script in the qvf file.

Cheers

Cameron

[petter · Accepted solution]

You don't need a CROSSTABLE prefix to achieve this - it is simpler to just load the table twice from a resident raw table:

RAW_TABLE:
LOAD 
  *
  FROM
[MileStone_Breach_Empty_Details_Data.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

DATA:
LOAD 
  BusinessDate, 
  Milestone, 
  Report, 
  Portfolio, 
  FeedId, 
  [Source SLA] AS sla, 
  [Source Start] AS [Time Start], 
  [Source End] AS [Time End], 
  [Source Status] AS [Minutes], 
  'Source' AS System
RESIDENT
  RAW_TABLE;

LOAD 
  BusinessDate, 
  Milestone, 
  Report, 
  Portfolio, 
  FeedId, 
  [MaRRS SLA] AS sla, 
  [MaRRS Start] AS [Time Start], 
  [MaRRS End] AS [Time End], 
  [MaRRS Status] AS [Minutes],
  'Marrs' AS System
RESIDENT
  RAW_TABLE;

DROP TABLE RAW_TABLE;

[petter]

Or if you hate to load the data twice after a resident load you could simply use WHILE to produce two rows out per row in:

DATA:
LOAD 
  BusinessDate, 
  Milestone, 
  Report, 
  Portfolio, 
  FeedId, 
  If( IterNo() = 1 , [Source SLA] , [MaRRS SLA] ) AS sla,
  If( IterNo() = 1 , [Source Start] , [MaRRS Start] ) AS [Time Start],
  If( IterNo() = 1 , [Source End] , [MaRRS End] ) AS [Time End], 
  If( IterNo() = 1 , [Source Status] , [MaRRS Status] ) AS [Minutes],
  If( IterNo() = 1 , 'Source' , 'Marrs' ) AS System
FROM
  [MileStone_Breach_Empty_Details_Data.csv]
  (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHILE
  IterNo()<=2;

[petter]

If you want to refer to the columns not by name but by number you could do this:

DATA:
LOAD
  @1 AS BusinessDate,
  @2 AS Milestone,
  @3 AS Report,
  @4 AS Portfolio,
  @5 AS FeedId,
  @6 AS test,
  Pick( IterNo() , @6  , @10 ) AS sla,
  Pick( IterNo() , @7  , @11 ) AS [Time Start],
  Pick( IterNo() , @8  , @12 ) AS [Time End],
  Pick( IterNo() , @9  , @13 ) AS [Minutes],
  Pick( IterNo() , 'Source' , 'Marrs' ) AS System
FROM
  [MileStone_Breach_Empty_Details_Data.csv]
  (txt, codepage is 1252, no labels,Header is 1 line, delimiter is ',', msq)
WHILE
  IterNo()<=2;

[Anonymous]

Thanks the first 2 i tried and work really well.

I had one more requirement, as mentioned, how to loop through a file directory so as to consume multiple files, ie. lib://Qlik/SLA Manual Load\.

[Anonymous]

And I have a number of other transformations that need to occur. When I tried to do this it say that it can find MaRRs Status. Maybe there is also a better way to write this. I went to use OR like i would in excel and it did not like it.

if([Source Status]='NULL','N/A',if([Source Status]<0,'Breach',if([MaRRs Status]='NULL','N/A',if([MaRRs Status]<0,'Breach','Ontime')))) as [Status]

[Anonymous]

Found the issue that it is spelt MaRRS not MaRRs but there is probably a better was of writing this.

[Anonymous]

Also, how do i get my Primary key to work as it is not picking up System. Maybe an if statement and then left trim function? Is there a prettier way than that?  

[BusinessDate]&[Milestone]&[System]&[Report]&[Portfolio]&[FeedId] AS [Primary Key]

[petter]

It is good practice to always use a separator character between concatenated fields when constructing a key:

[BusinessDate]&'|'&[Milestone]&'|'&[System]&'|'&[Report]&'|'&[Portfolio]&'|'&[FeedId] AS [Primary Key]

You can also use AutoNumber and AutoNumberHash128 and AutoNumberHash256 functions to create keys.

http://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/CounterFunctions/auto...

[petter]

The load statement supports wildcards in the filename:

The filename may contain the standard DOS wildcard characters ( * and ? ). This will cause all the matching files in the specified directory to be loaded.

So something like LIB://Qlik/SLA Manual Load/*.* should work and load all the files in the folder.