SQL Connection and Script Format

[EvanBarrick]

I have a table of data that is populated as such:

UserID                        PropertyName                                PropertyValue

1234                             FristName                                        John

1234                             LastName                                        Smith

1234                              City                                                     Omaha

1234                             Street                                               Pine

How can I structure my script so as to get a more traditional table such as below

User ID          FirstName                          LastName            City                  Street

1234               John                                     Smith                    Omaha            Pine

[Mauritz_SA]

Hi there

I don't have QS open so can't validate syntax, but try something like:

Temp:
Load
*
From YOURTABLE;

Noconcatenate
Newtable:
Load Distinct
UserId
Resident Temp;

Left join (NewTable)
Load
UserId,
PropertyValue As Firstname
Resident Temp
Where
PropertyName = 'Firstname';

Left join (NewTable)
Load
UserId,
PropertyValue As Lastname
Resident Temp
Where
PropertyName = 'Lastname';

Left join (NewTable)
Load
UserId,
PropertyValue As City
Resident Temp
Where
PropertyName = 'City';

Left join (NewTable)
Load
UserId,
PropertyValue As Street
Resident Temp
Where
PropertyName = 'Street';

Drop Table Temp;

If you will have a dynamic number of PropertyNames then you can load a list of distinct PropertyNames into a table and loop through them and left joining the PropertyName to the existing table every time.

Good luck!
Mauritz

[jayanttibhe]

Hi,

You need to use Generic Load and then Combine the resultant Tables. You can follow this simple code and execute in DEBUG mode to understand more.

Temp:
LOAD * INLINE [
    UserID, PropertyName, PropertyValue
    1234, City, Omaha
    1234, FristName, John
    1234, LastName, Smith
    1234, Street, Pine
];

Test:
LOAD 
Distinct UserID
Resident Temp;

Test_Gen:
Generic Load *
Resident Temp;

FOR i = NoOfTables()-1 to 0 STEP -1 
  LET vTable=TableName($(i)); 
  IF WildMatch('$(vTable)', 'Test_Gen.*') THEN 
    LEFT JOIN (Test) LOAD * RESIDENT    [$(vTable)]; 
    DROP TABLE  [$(vTable)]; 
  ENDIF 
NEXT i

DROP Table Temp;
vTable=;

Exit SCRIPT;

[bing_jville]

Qlik has Crosstable function
https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...