Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EvanBarrick
Creator
Creator

SQL Connection and Script Format

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

3 Replies
Mauritz_SA
Partner - Specialist
Partner - Specialist

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
Creator III
Creator III

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;